Forum Discussion

ajl_ahmed's avatar
ajl_ahmed
Iron Contributor
Jul 09, 2022
Solved

Combine four columns (multiple columns) vertically into one column

I have four columns with variable heights (I do not know How height), so I need a formula to dynamically discover the height of each column and combine the contents of each column vertically in a new...
excel
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 09, 2022

    ajl_ahmed 

    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),
))))
)
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 09, 2022

PeterBartholomew1 

One assumption we are on Beta. But here is another assumptions we are working with structured tables, not ranges,

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 10, 2022

SergeiBaklan 

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.

Resources