Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

1st and 2nd non-zero values in a row

Posted by Michael on December 04, 2001 10:33 AM

I have a row of data approx 100 columns wide. The data is either a zero or text:
0 0 0 0 0 apple 0 0 0 0 0 orange 0 0 0 0 0

Is there a formula that can sit at the end of the row and give me the first non-zero value in the row and another formula that gives me the second non-zero value?

Check out our Excel Resources

Re: 1st and 2nd non-zero values in a row

Posted by Mark W. on December 04, 2001 12:01 PM

1st non-zero value...

{=INDEX(A1:Q1,,SMALL(IF(ISTEXT(A1:Q1),ISTEXT(A1:Q1)*COLUMN(A:Q),""),1))}

2nd non-zero value...

{=INDEX(A1:Q1,,SMALL(IF(ISTEXT(A1:Q1),ISTEXT(A1:Q1)*COLUMN(A:Q),""),2))}

Note: These are array formulas which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.

why does the first data have to be in column a?

Posted by Michael on December 04, 2001 1:09 PM

I noticed that the formula doesn't work if you try to go b1:r1. However if I go a1:q1 it works. Any idea why?

Re: why does the first data have to be in column a?

Posted by Mark W. on December 04, 2001 1:20 PM

{=INDEX(B1:R1,,SMALL(IF(ISTEXT(B1:R1),ISTEXT(B1:R1)*COLUMN(B:R),""),1))}

...should work just fine! Don't forget to enter
this array formula using the Control+Shift+Enter
key combination.

Oops! Now, I see your problem... Use this instead...

Posted by Mark W. on December 04, 2001 1:36 PM

{=INDEX(B1:R1,,SMALL(IF(ISTEXT(B1:R1),ISTEXT(B1:R1)*COLUMN(B:R)-COLUMN(B:B)+1,""),1))}

If you prefer... You can also use...

Posted by Mark W. on December 04, 2001 1:39 PM

{=INDIRECT(ADDRESS(ROW(),SMALL(IF(ISTEXT(B1:R1),ISTEXT(B1:R1)*COLUMN(B:R),""),1)))} {=INDEX(B1:R1,,SMALL(IF(ISTEXT(B1:R1),ISTEXT(B1:R1)*COLUMN(B:R)-COLUMN(B:B)+1,""),1))} : {=INDEX(B1:R1,,SMALL(IF(ISTEXT(B1:R1),ISTEXT(B1:R1)*COLUMN(B:R),""),1))} : ...should work just fine! Don't forget to enter

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.