Combining data organised in rows with varying number of columns into a data set of one col

Hi,

Not sure I understand. Your desired results comprise two columns, not one?

Regards

Assuming your original data is enclosed by the range A1:G10, enter this array** formula in e.g. H1 and copy down until you start to get blanks for the results:

=IFERROR(INDEX(INDEX($A$1:$G$10,,MATCH(TRUE,MMULT((--(TRANSPOSE(COLUMN($A$1:$G$10))>=COLUMN($A$1:$G$10))),SUBTOTAL(3,OFFSET($A$1:$A$10,,ROW(INDIRECT("1:"&COLUMNS($A$1:$G$10)))-1,,)))>=ROWS($1:1),0)),SMALL(IF((1-(INDEX($A$1:$G$10,,MATCH(TRUE,MMULT((--(TRANSPOSE(COLUMN($A$1:$G$10))>=COLUMN($A$1:$G$10))),SUBTOTAL(3,OFFSET($A$1:$A$10,,ROW(INDIRECT("1:"&COLUMNS($A$1:$G$10)))-1,,)))>=ROWS($1:1),0))="")),ROW($A$1:$A$10)-MIN(ROW($A$1:$A$10))+1),IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+MMULT((--(TRANSPOSE(COLUMN($A$1:$G$10))>=COLUMN($A$1:$G$10))),SUBTOTAL(3,OFFSET($A$1:$A$10,,ROW(INDIRECT("1:"&COLUMNS($A$1:$G$10)))-1,,)))),ROWS($1:1)))),"")

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

You're welcome!

That formula is quite old so I imagine it could be improved upon now!

Why have you changed your Username, by the way? Have you informed a moderator that you have two accounts (if that is the case)?

Regards

Of course it is a fantatic formula of XOR LX in #4.

But with only 2 columns, why not just cut / paste column B below the last value in column A?

Originally Posted by oeldere

Of course it is a fantatic formula of XOR LX in #4.

But with only 2 columns, why not just cut / paste column B below the last value in column A?

..............