Finding and extracting populated cells in a column

[JONNY981]

Hi,

If I have three columns, A,B and C, and there are some rows populated in the range from 1 to about 50000 in the actual sheet (highlighted orange in the example sheet) how can I get excel to search for these populated cells and paste them into a table (highlighted yellow in the example sheet) so that they are all together? Example sheet is provided.

Thanks!

[NBVC]

In D2:

=IF(A2<>"",COUNT(D$1:D1)+1,"")

copied down to index the records


In E2:

=Max(D:D) to get total count

in F2:

=IF(ROWS($A$1:$A1)>$E$2,"",INDEX(A:A,MATCH(ROWS($A$1:$A1),$D:$D)))

copied down and across to extract records.

[snb]

Please Login or Register  to view this content.

[JONNY981]

Thanks a lot!