Find Second Non-Zero Value In A Column

[mlarson]

Hi there, I'd like to find the second non-zero value in a range (A13:A200) and have the output be in A9. So, in the example below, "Wilson, John" would be the output in A9. Hope this is easy. Thanks in advance!

0
0
0
Smith, Joe
0
0
Wilson, John
Jones, Sally
0
0
Johnson, Eric
0
Clark, Bill
0

 

[Peter_SSs]

Try

Book1
	A
9	Wilson, John
10
11
12
13	0
14	0
15	0
16	Smith, Joe
17	0
18	0
19	Wilson, John
20	Jones, Sally
21	0
22	0
23	Johnson, Eric
24	0
25	Clark, Bill
26	0
27
2nd Value

Cell Formulas
Range	Formula
A9	=INDEX(A13:A200,AGGREGATE(15,6,(ROW(A13:A200)-ROW(A13)+1)/(A13:A200<>0),2))

 

[mlarson]

Thank you. The "0" in cells is produced by a formula and that might be messing up the results. The formula provided seems to count Smith as 4th, Wilson as 7th, etc (so it's counting the 0's). Is there a way to rectify this while still keeping the formulas that produce the "0" cells (don't want to convert to values only).

 

[Peter_SSs]

The "0" in cells ...

If the cells contain "0" rather than 0 then try making this modification

=INDEX(A13:A200,AGGREGATE(15,6,(ROW(A13:A200)-ROW(A13)+1)/(A13:A200<>"0"),2))

 

[mlarson]

I'm sorry, I should have been more clear. The cells contain 0, not "0"... any ideas on why the formula isn't working?

 

[Fluff]

Peter_SSs formula works for me, but if your interested in a VBA solution try

Sub NonZero()
   Range("A9").Value = Range("A13:A200").SpecialCells(xlFormulas, xlTextValues).Areas(2)(1)
End Sub

 

[mlarson]

Thanks, Fluff, but I'd love to do it as a formula in a cell instead of VBA. I'll rephrase the problem I'm encountering...
The 0 in cells is produced by a formula and that might be messing up the results. The formula provided seems to count Smith as 4th, Wilson as 7th, etc (so it's counting the 0's). Is there a way to rectify this while still keeping the formulas that produce the 0 cells (don't want to convert to values only).

 

[Fluff]

As Peter's formula worked for me, I'm not sure why it's not working for you.
Could you post the formula that you use in A13:A200?

 

[mlarson]

As Peter's formula worked for me, I'm not sure why it's not working for you.
Could you post the formula that you use in A13:A200?

Sure.
In A13... =IF(AND($F13="Yes",A$12>=$J13,A$12<=$K13),$D13,"0")
In A14... =IF(AND($F14="Yes",A$12>=$J14,A$12<=$K14),$D14,"0")
etc.

 

[Fluff]

Either remove the quotes from the 0 in your formula , or use the formula in post#4