Something like this array* formula:

=IF(ROWS($B$1:B1)COUNTA(A:A),"",INDEX(A:A,SMALL(I F(ISTEXT(A$1:A$2000),ROW(A$1:A$2000)),ROWS($B$1:B1 ))))

Note that the ROWS function is only in there to keep track of how many
records have been used, it is not actually referring to any data. You need
only adjust the A$1:A$2000 and A:A callouts to the correct range to suit the
formula to your situation.

*Formula must be confirmed using Ctrl+Shift+Enter, not just Enter.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"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.