with your data in range A1:A1000 in Sheet1
one way might be to use this array-formula (insert with CTRL+SHIFT
+ENTER):

=OFFSET(Sheet1!$A$1,SMALL(IF(LEN(Sheet1!$A$1:$A$10 00),ROW(Sheet1!$A
$1:$A$1000)),ROW())-1,)

pls click YES if it helped


On 10 Lis, 14:28, James wrote:
Hi all

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?!

--
James.