how to get first non zero value in an array and display all values after that

[helloexcel07]

Hello,
Hoping someone can help me.
I need to check a row full of numerical values. What is the formula to retrieve the first non-zero value and then display all values after that in original order.
Thank you!

[JohnTopley]

Try

Assuming data in column A

in B1

=IFERROR(INDEX(A:A,SMALL(IF($A$1:$A$100<>0,ROW($A$1:$A$100),""),ROWS($A$1:A1))),"")

Enter with Ctrl+Shift+Enter

Drag down until you get blank cells.

[Tony Valko]

What version of Excel does this have to work in?

[Phuocam]

For Excel 2003:

=IF(ROWS($A$1:A1)>COUNTIF($A$1:$A$100,">0"),"",
INDEX(A:A,SMALL(IF(ISNUMBER($A$1:$A$100)*($A$1:$A$100<>0),ROW($A$1:$A$100),""),ROWS($A$1:A1))))

Enter with Ctrl+Shift+Enter

[helloexcel07]

thanks all for your replies. It seems your responses assume my data is in column A. how would the formula look like if the data array in in row 2 starting in cell C2 ending in BV2
I used this formula to find the first value above 0 in row 2, my challenge is to find and display all values after this one (in the original order).

=INDEX(C$2:BV$2,MATCH(TRUE,INDEX(C$2:BV$2>0,),0))

[JohnTopley]

Try

=IFERROR(INDEX($C$2:$BV$2,SMALL(IF($C2:$BV2<>0,COLUMN($C$2:$BV$2)-COLUMN($C$2)+1,""),COLUMNS($C:C))),"")

Enter with Ctrl+Shift+Enter

[Tony Valko]

I'll ask again, what version of Excel does this have to work in?

If this was your data:

Data Range

	C	D	E	F	G	H	I	J
1	----	----	----	----	----	----	----	----
2	0	0	22	0	6	41	0	15

What results do you expect?

Do you want: 22, 6, 41, 15 or, do you want: 22, 0, 6, 41, 0, 15?

Also, where do you want these results to appear? Across a row? Down a column? Tell us the cell address of the first cell where the results should appear and what direction it should follow.

[helloexcel07]

Thank you all! I appreciate your help!! I transposed the data and used the formula with Row. Have a great Sunday! and I learned something NEW!

[helloexcel07]

I have Microsoft Office Professional 2010

[helloexcel07]

I expect data to appear in K2 and the result is 22, 0, 6, 41, 0, 15

As noted -- thanks all-- I got my result. Appreciate it.

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Tony Valko]

Good deal. Thanks for the feedback!

[gdowbiggin]

Hi - looking for assistance with the same issue. I want my output to be 22, 0, 6, 41, 0, 15 and I use version Professional Plus 2013. I havent had any luck with the above formulas and I can't transpose my data.
My data is across a row and I want it to be displayed across a row (22, 0, 6, 41, 0, 15).
Thanks.