Tom’s Tutorials For Excel: Listing Noncontiguous Data Without Blanks

Tom’s Tutorials For Excel: Listing Noncontiguous Data Without Blanks

Here’s a formula solution to listing a noncontiguous list of items while skipping the blank cells.

In the picture, a list of items is in A1:A20, along with blank cells. In cell D1 and copied down as needed is this array formula to re-list what is in column A, skipping the blank cells:
=INDIRECT("A"&SMALL(IF(LEN($A$1:$A$20)=0,"",ROW($1:$20)),ROW(A1)))

Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
7 comments on “Tom’s Tutorials For Excel: Listing Noncontiguous Data Without Blanks
  1. Sarika says:

    not able to get output as expected.
    i have added below 5 column and applied formula =INDIRECT(“A”&SMALL(IF(LEN($A$1:$A$5)=0,””,ROW(1:5)),ROW(A1)))
    but didnt get it

    Apples

    Peaches

    Pears

    • Tom Urtis says:

      Are you certain that you confirmed the formula to the cell by pressing Ctrl+Shift+Enter, and not just with Enter, as I said in my directions for how this works?

  2. Zoran says:

    Nice formula. I would only have replaced the INDIRECT with the INDEX, by making it bit more flexible + non-volatile. Additionally, we don’t need to declaring the empty string since the FALSE does the same (to the SMALL). Therefore, as the final formula we might have: =INDEX($A$1:$A$20,SMALL(IF(LEN($A$1:$A$20),ROW($1:$20)),ROW(A1)))

  3. Jessica C says:

    Your formula works flawlessly when I am working within the same spreadsheet, however when I change the data range to a different spreadsheet within the same work book it stops working. Any suggestions?

    These are the alterations I made to the formula:

    =INDIRECT(“I”&SMALL(IF(LEN(‘Cask Inventory’!$I$6:$I$81)=0,””,ROW(‘Cask Inventory’!$6:$81)),ROW(‘Cask Inventory’!I6)))

    Any input would be greatly appreciated.

    Thank you,
    Jessica

    • Zack says:

      =INDIRECT(“‘Cask Inventory’!I”&SMALL(IF(LEN(‘Cask Inventory’!$I$6:$I$81)=0,””,ROW(‘Cask Inventory’!$6:$81)),ROW(‘Cask Inventory’!I6)))

      Try this

    • Zack says:

      Try This

      =INDIRECT(“‘Cask Inventory’!I”&SMALL(IF(LEN(‘Cask Inventory’!$I$6:$I$81)=0,””,ROW(‘Cask Inventory’!$6:$81)),ROW(‘Cask Inventory’!I6)))

Leave a Reply to Zoran Cancel reply

Your email address will not be published. Required fields are marked *

*