Formula To Create a List Without Spaces From Data With Spaces

Hey everyone, hope someone could help me out with this as I have been trying without luck for hours now!

I have a column with Names and blank cells and want to consolidate the names into a column without the spaces.

Please see screenshot here: https://imgur.com/a/WxJU9 I want the names in column L to appear in Column J without the spaces.

I know I can filter out the blanks but I am trying to automate something and don't wanna do manual filters.

Thanks so much for your help.

Cheers

Hi Nick,

Select the column L, press F5 then click on 'Special' -select 'Blanks' > Ok.
In the 'Home' tab, Delete > Delete sheet rows.

Hi, welcome to the forum

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

with helper column
M1=COUNTA(L$1:L1) Try this and copy towards down as much as possible
J1=IFERROR(INDEX(L:L,MATCH(ROWS(J$1:J1),M:M,0)),"") Try this and copy towards down

Without helper column
J1=IFERROR(INDEX($L$1:$L1000,SMALL(INDEX((L$1:L$1000="")*10^10+ROW($L$1:$L$1000),0),ROWS(J$1:J1))),"")
Try this and copy towards down