CODEWITHSUNDEEP
excelexcel-2007excel-formula
mooseman
mooseman

Reputation: 2017

Formula to return column header if there is data in the column

I need a formula to return column header if there is data in the column, any data. But I also need to return the second, third, fourth header with data too

In column A row 2, I have =IF(M8<>"",$M$1,IF(N8<>"",$N$1,IF(O8<>"",$O$1,IF(P8<>"",$P$1,IF(Q8<>"",$Q$1,IF(R8<>"",$R$1,IF(S8<>"",$S$1,IF(T8<>"",$T$1,""))))))))

This returns the row 1 header for the first instance of data in row 2 columns M thru T But in B2, I need the second header with data in the column.

I need the results in yellow (1st, 2nd, 3rd, 4th) from the array to it's left

 1St match   2nd        3rd         4th            Column M    Column N    Column O    Column P    Column Q    Column R    Column S    Column T
Column M    Column O    Colum R                      Data                  data2                               Data3        
Coulmn N    Column O    Column Q    Column T                    Data        Data2                   data3                               data4

Would use match/index but can't increment it to the second or 3rd match Thanks

Upvotes: 1

Views: 3026

Answers (2)

PowerUser
PowerUser

Reputation: 11791

If you only need to do this once, this method should work. If you need to do it by automation, this will need some modification:

  1. Make a new column called ListAllCols (or whatever). Use this formula.

    =IF(E2<>"",","&E$1,"")
    & IF(F2<>"",","&F$1,"")
    & IF(G2<>"",","&G$1,"")
    & IF(H2<>"",","&H$1,"")

    Using your example, the first row should return "Column M, Column O, Column R"

  2. Select this column, then Copy & Paste Values to get rid of the equations.

  3. Using Excel 2007's Text to Columns feature (Data->Data Tools->Text to Columns), split this out into 3 separate columns by specifying the comma as the delimiter.

Upvotes: 1

barry houdini
barry houdini

Reputation: 46361

You can use this "array formula" in A2 confirmed with CTRL+SHIFT+ENTER and copied across

=IFERROR(INDEX($M$1:$T$1,SMALL(IF($M8:$T8<>"",COLUMN($M8:$T8)-COLUMN($M8)+1),COLUMNS($A2:A2))),"")

when data runs out you get blanks

To confirm with CTRL+SHIFT+ENTER put formula in A2, press F2 key to select formula then hold down CTRL and SHIFT keys and press ENTER. Curly braces like { and } will appear around the formula in the formula bar - do that before you copy across

Upvotes: 1

Related Questions