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

very nice. 🙂

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

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?

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

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

=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

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