Forum Discussion
Combine four columns (multiple columns) vertically into one column
- Jul 09, 2022
It depends on which exactly version of Excel 365 you are, as variant
=LET( columnA,FILTER(A:A,A:A<>""), columnB,FILTER(B:B,B:B<>""), columnC,FILTER(C:C,C:C<>""), columnD,FILTER(D:D,D:D<>""), nA,ROWS(columnA), nB,ROWS(columnB), nC,ROWS(columnC), nD,ROWS(columnD), k,SEQUENCE(nA+nB+nC+nD), IF(k<=nA,INDEX(columnA,k), IF(k<=nA+nB,INDEX(columnB,k-nA), IF(k<=nA+nB+nC,INDEX(columnC,k-nA-nB), IF(k<=nA+nB+nC+nD,INDEX(columnD,k-nA-nB-nC), )))) )
Two further strategies, one of which is a serious contribution and the other 'tongue in cheek'. I treat any values that do not lie within a table or (for more static data) a named range as invalid. In the past I have even written macros to delete such unwanted data entries, recording only the identified data.
Here, I used a separate table for each column, so that the resulting combined data set could be expressed in the form
= VSTACK(TableA,TableB,TableC,TableD)
Note: VSTACK is only available on the Insider beta channel at the time of writing. It is quite possible to replace the built-in function by a further Lambda function 'VStackλ' and the code for that is already contained within Sergei's formula.
The other technique involved referencing each table as a Thunk (a Lambda function that returns its content if called with a blank parameter string). I then used VSTACK to combine these into an array
Tablesϑ
= VSTACK(
Thunkλ(TableA),
Thunkλ(TableB),
Thunkλ(TableC),
Thunkλ(TableD)
)
Thunkλ
= LAMBDA(x,LAMBDA(x))
The worksheet formula expands the array of Thunks
= REDUCE("Combined", Tablesϑ,
LAMBDA(acc,Tϑ, VSTACK(acc, Tϑ() ))
)
This formula works but is distinctly 'over the top'. I would only use the approach if the tables were distributed in different locations over multiple sheets and I needed to evaluate which Tables were to be used for multiple calculations.
- SergeiBaklanJul 09, 2022Diamond Contributor
One assumption we are on Beta. But here is another assumptions we are working with structured tables, not ranges,
- PeterBartholomew1Jul 10, 2022Silver Contributor
Very true. I do not think structured references should be a problem, after all that has been possible since Office 2007 and I think List Objects were available in some form even before that. Requiring insider beta does make the proposed solutions inaccessible to almost all however.
As for traditional spreadsheet methods, I have discarded them; I didn't like them at the time and I am even less inclined to use them now. I would have posted a VStackλ function but I think one of your posts pretty much did that.