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