excel problem formula to copy a vertical list but without blanks

%3CLINGO-SUB%20id%3D%22lingo-sub-2831609%22%20slang%3D%22en-US%22%3Eexcel%20problem%20formula%20to%20copy%20a%20vertical%20list%20but%20without%20blanks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2831609%22%20slang%3D%22en-US%22%3E%3CP%3Ewindows%2020H2%20Excel%202108%3C%2FP%3E%3CP%3EI%20have%20list%20with%20some%20blanks%20in%20B2-B12.%20i%20want%20to%20recreate%20the%20list%20without%20the%20blanks%20in%20D2-D12.%3C%2FP%3E%3CP%3EI%20got%20this%20formula%20from%20the%20internet%3A%3C%2FP%3E%3CP%3E%3DIF(ROW()-ROW(%24B%242%3A%24B%2412)%2B1%26gt%3BROWS(%24B%242%3A%24B%2412)-COUNTBLANK(%24B%242%3A%24B%2412)%2C%22%22%2C%20INDIRECT(ADDRESS(SMALL((IF(%24B%242%3A%24B%2412%26lt%3B%26gt%3B%22%22%2CROW(%24B%242%3A%24B%2412)%2CROW()%2BROWS(%24B%242%3A%24B%2412)))%2C%20ROW()-ROW(%24D%242%3A%24D%2412)%2B1)%2CCOLUMN(%24B%242%3A%24B%2412)%2C4)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20put%20formula%20in%20D2-D12.%20seems%20to%20work%20in%20D2%20(there%20is%20text%20in%20B2%20so%20it%20brings%20the%20text%20over).%3C%2FP%3E%3CP%3Eseems%20to%20reorder%20the%20list%20such%20that%20the%207%20non-blank%20cells%20are%20at%20the%20top%20and%20the%20blank%20ones%20at%20the%20bottom%20...%20which%20is%20what%20I%20want.%20BUT%20in%20cells%20D3-D8%20I%20get%20a%20%22%23NUM!%22%20error%20instead%20of%20the%20texts%20from%20non-blank%20cells%20B4%2CB5%2CB7%2CB9%2CB10%2CB11.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2831609%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2831623%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20problem%20formula%20to%20copy%20a%20vertical%20list%20but%20without%20blanks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2831623%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1181259%22%20target%3D%22_blank%22%3E%40Dave_Clark1155%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20have%20Excel%20in%20Microsoft%20365%20or%20Office%202021%3F%20If%20so%2C%20enter%20the%20following%20formula%20in%20D2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DFILTER(B2%3AB11%2CB2%3AB11%26lt%3B%26gt%3B%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20results%20will%20automatically%20spill%20to%20the%20rows%20below%20as%20far%20as%20needed.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2831640%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20problem%20formula%20to%20copy%20a%20vertical%20list%20but%20without%20blanks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2831640%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1181259%22%20target%3D%22_blank%22%3E%40Dave_Clark1155%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20older%20versions%20of%20Excel%20you%20can%20use%20this%20array%20formula%20confirmed%20with%20Ctrl%2BShift%2BEnter%2C%20then%20filled%20down%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX(%24B%242%3A%24B%2411%2CSMALL(IF(%24B%242%3A%24B%2411%26lt%3B%26gt%3B%22%22%2CROW(%24B%242%3A%24B%2411)-ROW(%24B%242)%2B1%2C%22%22)%2CROW(D2)-ROW(%24D%242)%2B1))%2C%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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 Replies

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

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