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