Header of a column containing the first non-blank cell

Question:

( by Guy Pursey )

I've included an example below to help illustrate what I would like to do.

 |E F G H I J K L M N ...

-+-----------------------

1|a d p g q f s z i j ...

2|    1           1   ...

3|        1   1       ...

4|1                   ...

5|            1   1   ...

In column D (not shown in example), I would like each cell below row 2 to show the value of the header cell of the column for which the first cell is not blank (or for which the first cell contains the number 1).

Here's how the above example would look if I had a successful formula in column D.

 |D E F G H I J K L M N ...

-+-----------------------

1|  a d p g q f s z i j ...

2|d   1           1   ...

3|q         1   1       ...

4|a 1                   ...

5|s             1   1   ...

To explain: because the first non-blank cell in E4:4 is E4, the header we want is 'a' (E1). Therefore D4 will show the header for the first non-blank cell--that is, 'a'. In E2:2 the first non-blank cell is F2. So we want D2 to show 'd' because that is the header for that column containing the cell. Etc.

I have tried using this formula but it simply shows a zero in all the cells of column D.

=INDEX($E$1:$1,MATCH(TRUE,INDEX((E2:2<>0),0),0))

=INDEX($E$1:$1,MATCH(TRUE,INDEX((E3:3<>0),0),0))

=INDEX($E$1:$1,MATCH(TRUE,INDEX((E3:3<>0),0),0))

Any idea where I could be going wrong and what the correct formula should be?

Any help greatly appreciated! I've been struggling with this...

Solution:

OPTION 1:

Have a look at the following screenshot of "Sheet1":

In the above sheet, I have the following formula in Cell D2:

=query(transpose($E$1:$N2);"select Col1 where Col"&row()&"=1 limit 1";0)

And if you have any other value than 1 (that is non blank cell) then try this:
=query(transpose($E$1:$N2);"select Col1 where Col"&row()&" is not null limit 1";0)

and then drag the above formula to below cells.

OPTION 2:

Single formula solution:
Have a look at the following screenshot of "Sheet2":

In the above sheet, I have the following formulas in Cell D2:

=arrayformula(left(if(E2:E;E1;"")&if(F2:F;F1;"")&if(G2:G;G1;"")&if(H2:H;H1;"")&if(I2:I;I1;"")&if(J2:J;J1;"")&if(K2:K;K1;"")&if(L2:L;L1;"")&if(M2:M;M1;"")&if(N2:N;N1;"")))

And if you have any other value than 1 (that is non blank cell) then try this:

=arrayformula(left(if(E2:E<>"";E1;"")&if(F2:F<>"";F1;"")&if(G2:G<>"";G1;"")&if(H2:H<>"";H1;"")&if(I2:I<>"";I1;"")&if(J2:J<>"";J1;"")&if(K2:K<>"";K1;"")&if(L2:L<>"";L1;"")&if(M2:M<>"";M1;"")&if(N2:N<>"";N1;"")))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive