Thanks guys very helpful. Another challenge for you though, what if there are
2 columns not just one?!

--
James.


"Max" wrote:

Assume your source data is in Sheet1, in A2 down to A1000

In another sheet,
In A2: =IF(TRIM(Sheet1!A2)="","",ROW())
Copy A2 down to cover the same extent as the source, ie down to A1000.
Hide/minimize col A. The TRIM is optional, but I've thrown it in here to
rule out possibility of any white spaces within the source data.

Then place in B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(A:A,ROWS($1:1))))
Copy B2 down to cover the max expected extent of results, ie down to B11
(since you expect only max 10 rows of results). The expected results will
appear all neatly packed at the top. Success? hit the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"James" wrote:
I have a column with 1,000 rows but only 10 of the rows contain any data and
they are spread out across the 1,000. On a seperate sheet I want to list
those 10 in a row together but I don't want to copy all 1,00 across and use
data sort as it needs to be formula based.

Any ideas?!