1

I have a spreadsheet with 20+ columns and I'm trying to get on each row a list of the columns that are populated. For example:

Owner     Item List            Item1 Item2 Item3 Item4
Me        Item1,Item2           3      4        
You       Item1,Item3,Item4     2            1     2
Her       Item2,Item3                  1     1  
Him       Item1,Item4           5                  6

For the "Me" row, Item1 and Item2 are populated, so the Item List is "Item1,Item2" (any delimiter is fine).

Is it possible to accomplish this without VBA?

abeardneglected
  • 11
  • 1
  • 2
  • [I would take a look at this](http://stackoverflow.com/questions/20335906/return-a-comma-separated-list-from-an-array-formula) question I asked when I started on SO. I was looking to solve a similar problem as you -- it might point you in the right direction. – Adam Smith Dec 20 '13 at 17:54
  • Thanks. It does look like there's some potential there. I'll play around with it. – abeardneglected Dec 21 '13 at 05:41

1 Answers1

2

enter image description here

Step 1 : figure out the items need to include for each owner(Grab Items)

In cell G2, put in the following formula:

=IF(ISNUMBER(C2)=TRUE,CONCATENATE(C$1,";"),"")

Apply it to through G2:J5. This will put the item name in corresponding cell if there is a number in C2:F5

Step 2 : Concatenate and get list of items: In cell K2 type in:

=CONCATENATE(G2,H2,I2,J2)

apply it through K2:K5

Cici
  • 1,407
  • 3
  • 13
  • 31
  • Thanks - that does get me there. The real spreadsheet has 42 columns, so the concatenation in step 2 will be long, but doable. – abeardneglected Dec 21 '13 at 05:33
  • @abeardneglected yeah, excel's concatenate function does not accept range as input... you might try these methods...http://stackoverflow.com/questions/8135995/how-to-merge-all-column-into-one-cell-in-excel – Cici Dec 21 '13 at 19:26