Creating a contiguous list from a non contiguous list of items

I would be grateful if someone could help with taking a list of non-contiguous entries and recreating those same entries in a contiguous list in another column as shown below:

Non-contiguous list:
Peter
Paul
Tracy

John

Abigail
Darcy

James


Contiguous list:
Peter
Paul
Tracy
John
Abigail
Darcy
James


I need to simpley recreate the (non-contiguous) list in another column where the blanks are missed out/ ignored.

Any help will be much appreciated!

Thanks
Tony

Hi,

here's one way to do it:
1. select the whole column by clicking the column header.
2. use autofilter (data/filter/autofilter)
3. filter for blanks (click the little mark that appears in the top cell and select "Blanks")
4. select/highlight all the selected rows --> right click --> delete.

Now you should only have the cells with data in it.

Thanks for the quick reply but I need this done using formulas as opposed to a manual operation or any type of scripting. I should have perhaps explained better in my question

You sir are a star and a major league array formula master! Thank you

That formula does it.

That also works a treat. Thank you.

Two great replies.