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