Multiple columns into one column without blanks

Question

I'm using Microsoft Excel 2010. I have multiple columns with data in them, and some blank cells too. I'm using this formula:

=INDEX($A$101:$F$200;MOD(ROW()-ROW($A$201)-1;ROWS($A$101:$F$200))+1;INT((ROW()-ROW($A$201)-1)/ROWS($A$101:$F$200))+1)

to merge them into a single long column. However, it adds there the blank cells too. Can this formula be modified to leave out those blank cells, and just merge the cells with data in them?

Example picture:

Answer 1

After much struggling, I have found something that works, though it uses two work columns (which will obviously be hidden before the sheet is used).

I tested with Excel 2007, which shows 0 instead of a blank cell when index() or indirect() returns a blank cell: I could have removed these with conditional formatting, but I have left them in so as not to obscure how the formulae are working:-

I have used your test data, so the formula copied down from D5 is:

=INDEX($A$5:$B$8,MOD(ROW()-ROW($A$5),ROWS($A$5:$B$8))+1,INT((ROW()-ROW($A$5))/ROWS($A$5:$B$8))+1)

Column D is now used as a work column, and another work column F is created, with F5 containing:

=MATCH("*",$D$5:$D$12,0)+ROW()-1

and copied down from F6:

=MATCH("*",INDIRECT(ADDRESS(F5+1,4,1)&":$D$12"),0)+F5

The significant points here are that:

• Wild-cards can be used when the third parameter of MATCH() is zero (unordered).
• "*" matches anything, but not the 0 returned by INDEX() for a blank cell.

Finally, H5 copied down is:

=IFERROR(INDIRECT("$D"&F5),"")

Here IFERROR() is used to catch the #N/A entries in column F after the last found entry.

After hiding the work columns, you get what you wanted:-

Note that, to prevent the interruption of the live column sequence, the work columns can be outside the main sheet area (eg Y and Z) or on a separate sheet, though the latter will complicate the formulae even further with sheet references.

Also, it may be possible to use only one work column, but the formulae would be of hugely increased complexity, making support very difficult.

Note also that your formulae could be regarded as unnecessarily complicated, eg ROW($A$201) is always 201 and ROWS($A$101:$F$200) is always 100; in both cases it is because absolute addressing has been used - ROW() and ROWS() become useful when cells using relative addressing are copied.