excel problem formula to copy a vertical list but without blanks

[Dave_Clark1155]

windows 20H2 Excel 2108

I have list with some blanks in B2-B12. i want to recreate the list without the blanks in D2-D12.

I got this formula from the internet:

=IF(ROW()-ROW($B$2:$B$12)+1>ROWS($B$2:$B$12)-COUNTBLANK($B$2:$B$12),"", INDIRECT(ADDRESS(SMALL((IF($B$2:$B$12<>"",ROW($B$2:$B$12),ROW()+ROWS($B$2:$B$12))), ROW()-ROW($D$2:$D$12)+1),COLUMN($B$2:$B$12),4)))

i put formula in D2-D12. seems to work in D2 (there is text in B2 so it brings the text over).

seems to reorder the list such that the 7 non-blank cells are at the top and the blank ones at the bottom ... which is what I want. BUT in cells D3-D8 I get a "#NUM!" error instead of the texts from non-blank cells B4,B5,B7,B9,B10,B11.

[#2]

@Dave_Clark1155 

Do you have Excel in Microsoft 365 or Office 2021? If so, enter the following formula in D2:

=FILTER(B2:B11,B2:B11<>"")

The results will automatically spill to the rows below as far as needed.

[#3]

@Dave_Clark1155 

In older versions of Excel you can use this array formula confirmed with Ctrl+Shift+Enter, then filled down:

=IFERROR(INDEX($B$2:$B$11,SMALL(IF($B$2:$B$11<>"",ROW($B$2:$B$11)-ROW($B$2)+1,""),ROW(D2)-ROW($D$2)+1)),"")

See the attached version.