"bridgerbell" wrote:
i actually figured out how to exclude zeroes after i posted but couldn't
load the forum for some reason to remove my question.

Do not edit after you have posted. If you wish to clarify your own earlier
post, just post again as a reply to it. That way, your clarifications will
appear and be visible to all readers out there, not just to readers of
excelforum.

but, i have one more question:
i have a header row, so my first entries are in row 2.
your formulas only work if my entries start in row 1. if i put your
formulas in my spreadsheet with the header row, i get the second number
i want at the top instead of the first. how can i adjust the formula to
account for the numbers starting in row 2?

If source data in col A starts in A2 down ...

In B2:
=IF(ROW(A1)COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C: C,ROW(A1)),C:C,0)))

[ Same formula as previous. No change. ROW(A1) is always used in the
starting formula cell, irrespective of the row that the cell is in. ROW(A1)
is used as an incrementer when we copy down. It simply produces the number
series: 1,2,3 ... for the SMALL to pick off ]

In C2:
=IF(OR(A2={"",0}),"",IF(COUNTIF($A$2:A2,A2)1,"",R OW()))

[ OR(A2={"",0}) is just a shorter rendition of: OR(A2="",A2=0) ]

Then just select B2:C2, fill down as far as data is expected in col A
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---