# 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. ###### 7 comments on “Tom’s Tutorials For Excel: Listing Noncontiguous Data Without Blanks”
1. Brandi Leath says:

very nice. 🙂

2. 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?

3. 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)))

4. 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)))