CODEWITHSUNDEEP
excel
Minh Phan
Minh Phan

Reputation: 13

EXCEL - Get mutiple column headers if row is not blank

I have an excel file with the table below, there are about 20 columns and 100 rows

Header1 Header2 Header3 Header4 Header5
Name 1 A B AA
Name 2 A C
Name 3 B A AB A
Name 4 C A B

I need to get the column headers where cells have value and list them in a seperated sheet, seperated cell. Below is what I want.
Name 1 Header1 Header2 Header5
Name 2 Header2 Header4
Name 3 Header1 Header2 Header3 Header5
Name 4 Header2 Header3 Header4

Is there a function that I can use? VBA is okay too. Any help would be greatly appreciated. Thank you.

Upvotes: 1

Views: 882

Answers (1)

Harun24hr
Harun24hr

Reputation: 36880

With Excel 365 you can try-

=TRANSPOSE(FILTER(TRANSPOSE($B$1:$F$1),INDEX(TRANSPOSE($B$2:$F$5),,MATCH(H2,$A$2:$A$5,0))<>0))

enter image description here

Upvotes: 1

Related Questions