SOLVED

Iron Contributor

# 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 column without any blank cells. as explained below: Note that I am using Excel 365.

Column A          Column B        Columns C           Column D        Column E
A1                       B1                       C1                          D1                  A1

A2                       B2                       C2                                                 A2

A3                       B3                                                                            A3

A4                                                                                                       A4

B1

B2

B3

C1

C2

D1

Regards

13 Replies
best response confirmed by ajl_ahmed (Iron Contributor)
Solution

# Re: Combine four columns (multiple columns) vertically into one column

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),
))))
)``````

# Re: Combine four columns (multiple columns) vertically into one column

the latest one @Sergei Baklan

# Re: Combine four columns (multiple columns) vertically into one column

Many thanks, it is working

# Re: Combine four columns (multiple columns) vertically into one column

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.

# Re: Combine four columns (multiple columns) vertically into one column

@ajl_ahmed , you are welcome

# Re: Combine four columns (multiple columns) vertically into one column

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

# Re: Combine four columns (multiple columns) vertically into one column

``````Sub combine()

Dim i As Integer
Dim j As Integer
Dim maxrow As Long
Dim k As Long

Range("E:E").Clear

k = 1

For j = 1 To 4
maxrow = Cells(Rows.Count, j).End(xlUp).Row

For i = 1 To maxrow
Cells(k, 5).Value = Cells(i, j).Value
k = k + 1
Next i

Next j

End Sub``````

An alternative for older excel versions could be this code. In the attached file one can click the button in cell G2 to run the macro.

# Re: Combine four columns (multiple columns) vertically into one column

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.

# Re: Combine four columns (multiple columns) vertically into one column

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 :folded_hands:

# Re: Combine four columns (multiple columns) vertically into one column

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),
))))
)`````` Many Thanks

# Re: Combine four columns (multiple columns) vertically into one column

Hopefully such tasks will soon get so much easier and clearer.

``````= LET(
column, TOCOL(data),
blank,  ISBLANK(column),
FILTER(column,NOT(blank))
)``````

# Re: Combine four columns (multiple columns) vertically into one column

@ajl_ahmed , you are welcome