CODEWITHSUNDEEP
excelexcel-formula
abeardneglected
abeardneglected

Reputation: 11

Excel: Return all column headers with a value in the current row

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?

Upvotes: 1

Views: 7522

Answers (1)

Cici
Cici

Reputation: 1447

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

Upvotes: 2

Related Questions