returning column number

  • Thread starter Thread starter George Andersen
  • Start date Start date
G

George Andersen

Hello All,

Sure is a wealth of information here, I'm hoping someone can help me
out with a function to return the first column letter which contains a
value greater than 0. For Example:

A B C D E RESULT
1 TEST 0 0 3 1 "D"
2 TEST1 0 2 0 2 "C"
3 TEST2 1 6 0 3 "B"
4 TEST3 0 0 0 1 "E"
5 ETC...

Any direction is greatly appreciated,

George
 
Hi
one way to return the column number:
=MATCH(0,B1:E1,1)+1
and copy down

to get the column letter try
=LEFT(ADDRESS(1,MATCH(0,B1:E1,1)+1,4),1)
 
Hi George,

Here is one way

=CHAR(MIN(IF(B1:Y1>0,COLUMN(B1:Y1)))+64)

change Y1 to the last column, and commit with Ctrl-Shift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi change the formulas to
=MATCH(0,B1:E1,1)+2
or
=LEFT(ADDRESS(1,MATCH(0,B1:E1,1)+2,4),1)
 
This formula will return the cell address
of the match in the first row

=CELL("address",INDEX(1:1,MATCH(1,(1:1>0)*(ISNUMBER(1:1)),0)))

entered with ctrl + shift & enter

using your example it will return $D$1

copy down to return the other addresses
if by any chance the numbers are text use

=CELL("address",INDEX(A1:Z1,0,MATCH(1,(1:1>0)*(ISNUMBER(--(1:1))),0)))
 
Thank you.

The formula works, however I continuously get a "circular reference"
error.

Any Suggestions?

Thanks,
George
 
This worked to return the value in the left most cell, but I am trying
to get the column letter / number of the leftmost cell that has a
number greater than 0.
 
If your formula is in the same row you would get an error, however just
change the
references to something like A1:H1 or whatever the size of your data is
 
Frank,

This fails if no 0's.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Given that A1:E5 houses...

{"A","B","C","D","E";"TEST",0,0,3,1;"TEST1",0,2,0,2;"TEST2",1,6,0,3;"TEST3",
0,0,0,1}

the result area shows:

{"RESULT";"D";"C";"B";"E"}

Precisely the results you posted along with your data.

Maybe you meant:

=SUBSTITUTE(ADDRESS(1,COLUMN(INDEX(B2:E2,MATCH(TRUE,INDEX(B2:E2>0,1,0),0))),
4),"1","")

to be entered in F2.
 
No, Frank. The formula as set up does not require confirming with
control+shift+enter.
 
Hi Bob
correct.
For the OP: Though one can add some error checking to this formula I'd
recommend to use Peo's or Bob's solutions as they do not require this
kind of error checking
 
Hi Aladin
have to read formulas more carefully. You're of course right (nice
trick by the way)
Frank
 
In F2 enter & copy down:

=INDEX($B$1:$E$1,MATCH(TRUE,INDEX(B2:E2>0,1,0),0))
Click to expand...
...

Clever way to avoid array entry, but the INDEX call is unnecessary. Entering

=INDEX($B$1:$E$1,MATCH(TRUE,B2:E2>0,0))

as an array formula would produce the same result, and it should recalc faster.
 
That did the trick. Thank you.
-George




Given that A1:E5 houses...

{"A","B","C","D","E";"TEST",0,0,3,1;"TEST1",0,2,0,2;"TEST2",1,6,0,3;"TEST3",
0,0,0,1}

the result area shows:

{"RESULT";"D";"C";"B";"E"}

Precisely the results you posted along with your data.

Maybe you meant:

=SUBSTITUTE(ADDRESS(1,COLUMN(INDEX(B2:E2,MATCH(TRUE,INDEX(B2:E2>0,1,0),0))),
4),"1","")

to be entered in F2.
Click to expand...
 
Back
Top