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

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),
))))
)
  • ajl_ahmed's avatar
    ajl_ahmed
    Iron Contributor
    Jul 26, 2022

    SergeiBaklan 

    hi

    i have applied your formula which combine multiple columns into one column vertically. However, I have noticed that if one of columns not containing data , the formula is not working. Can you please do modification on it to work properly considering this case?

    many thanks 🙏 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 26, 2022

      ajl_ahmed 

      Straightforward way is to wrap by IFERROR

      =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,IFERROR(ROWS(columnA), 0),
nB,IFERROR(ROWS(columnB), 0),
nC, IFERROR(ROWS(columnC), 0),
nD,IFERROR(ROWS(columnD), 0),
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),
))))
)

      • ajl_ahmed's avatar
        ajl_ahmed
        Iron Contributor
        Jul 27, 2022
        Many Thanks
  • ajl_ahmed's avatar
    ajl_ahmed
    Iron Contributor
    Jul 09, 2022
    Many thanks, it is working

Resources