Combine columns into single column formulaically

[puzzlelover22]

Hi All,

Combine columns.xlsx

I've attached a sample for you to follow along with. I'm looking for a way to combine multiple columns into a single column. My initial attempt was to return a cell if it was not blank and as soon as the formula found a blank cell, increment the column to return begin the process again. The problem with this is that the initial references in my if statement aren't flexible enough to return to the top of the next column once it exhausts the data in the previous column (e.x. in the sample, I keep getting "North Carolina" when I want it to start returning the whole column). I'm thinking I need a ROW function that returns to 2:2 after a column runs out of data, but I can't seem to put it all together.

Anyway, thanks for your help!

Alex

[XOR LX]

Hi.

Perhaps you can adapt this to suit your needs?

http://excelxor.com/2014/08/22/advan...nd-discussion/

Regards

[Glenn Kennedy]

Try this out...

[XOR LX]

@Glenn Kennedy

But what if the number of columns to be considered was not 4 but, say, 10? Or 30?

Would you still create as many INDEX clauses as necessary into one mega-formula?

Regards

[Glenn Kennedy]

To be honest...

Probably (almost certainly) not...

Hey ho.