HELP how to stack multiple columns into one column. Using (formula or vba code)

[usui]

Hi masters,

Can anyone help me with a formula for stacking multiple columns data into one column. wherein it copies per column not per row.

sample here:

I hope anyone has an idea using excel 2016 here..please help me

 

[Max1616]

Give this a try:

Sub Consolidate()
Dim lrow As Long
Dim i As Long
Dim j As Long


For i = 2 To 4
    lrow = Cells(Rows.Count, i).End(xlUp).Row
    For j = 2 To lrow
        If Cells(j, i) <> "" Then
            Cells(j, i).Copy Cells(Cells(Rows.Count, 6).End(xlUp).Row + 1, 6)
        End If
    Next j
Next i

End Sub

 

[Eric W]

If the number of items in each column is the same:

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	Square	Col 1	Col 2	Col 3		Result
2	1	B1	C1	D1		B1
3	2	B2	C2	D2		B2
4	3	B3	C3	D3		B3
5	4	B4	C4	D4		B4
6	5	B5	C5	D5		B5
7	6	B6	C6	D6		B6
8	7	B7	C7	D7		B7
9						C1
10						C2
11						C3
12						C4
13						C5
14						C6
15						C7
16						D1
17						D2
18						D3
19						D4
20						D5
21						D6
22						D7
23
Sheet3

Cell Formulas
Range	Formula
F2:F23	F2	=IF(ROWS(F$2:F2)>COUNTA($B$2:$D$20),"",INDEX($B$2:$D$20,MOD(ROWS(F$2:F2)-1,COUNTA($A$2:$A$20))+1,INT((ROWS(F$2:F2)-1)/COUNTA($A$2:$A$20))+1))
Press CTRL+SHIFT+ENTER to enter array formulas.

If the number of items in each column can be different:

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	Square	Col 1	Col 2	Col 3		Result
2	1	B1	C1	D1		B1
3	2	B2	C2	D2		B2
4	3	B3	C3	D3		B3
5	4	B4		D4		B4
6	5	B5		D5		B5
7	6			D6		C1
8	7					C2
9						C3
10						D1
11						D2
12						D3
13						D4
14						D5
15						D6
16
Sheet2

Cell Formulas
Range	Formula
F2:F16	F2	=IF(ROWS(F$2:F2)>COUNTA($B$2:$D$20),"",INDEX($1:$1000,MOD(SMALL(IF($B$2:$D$20<>"",COLUMN($B$2:$D$20)*1000+ROW($B$2:$D$20)),ROWS(F$2:F2)),1000),INT(SMALL(IF($B$2:$D$20<>"",COLUMN($B$2:$D$20)*1000+ROW($B$2:$D$20)),ROWS(F$2:F2))/1000)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[usui]

Give this a try:

Sub Consolidate()
Dim lrow As Long
Dim i As Long
Dim j As Long


For i = 2 To 4
    lrow = Cells(Rows.Count, i).End(xlUp).Row
    For j = 2 To lrow
        If Cells(j, i) <> "" Then
            Cells(j, i).Copy Cells(Cells(Rows.Count, 6).End(xlUp).Row + 1, 6)
        End If
    Next j
Next i

End Sub

It works well, um how about there are 6 columns sir?..and I want the result to show up on Column 8 or H2?

 

[Max1616]

Try this. I added two variables that allow you to easily change the # of columns and the destination of the output:

Sub Consolidate()
Dim lrow As Long
Dim i As Long
Dim j As Long
Dim DestCol As Integer
Dim NumOfCol As Integer

DestCol = 8 'Put the column number you'd like the results to go
NumOfCol = 5 'Put the number of columns that contain data

For i = 2 To NumOfCol + 1
    lrow = Cells(Rows.Count, i).End(xlUp).Row
    For j = 2 To lrow
        If Cells(j, i) <> "" Then
            Cells(j, i).Copy Cells(Cells(Rows.Count, DestCol).End(xlUp).Row + 1, DestCol)
        End If
    Next j
Next i

End Sub

 

[usui]

Try this. I added two variables that allow you to easily change the # of columns and the destination of the output:

Sub Consolidate()
Dim lrow As Long
Dim i As Long
Dim j As Long
Dim DestCol As Integer
Dim NumOfCol As Integer

DestCol = 8 'Put the column number you'd like the results to go
NumOfCol = 5 'Put the number of columns that contain data

For i = 2 To NumOfCol + 1
    lrow = Cells(Rows.Count, i).End(xlUp).Row
    For j = 2 To lrow
        If Cells(j, i) <> "" Then
            Cells(j, i).Copy Cells(Cells(Rows.Count, DestCol).End(xlUp).Row + 1, DestCol)
        End If
    Next j
Next i

End Sub

Nothing happen when I tried this one sir. =(

 

[Max1616]

Do your headers start in column 1 and your Square is in column A? The code requires both of these things to be true.

This code is working in the test wb I'm using.

 

[usui]

Do your headers start in column 1 and your Square is in column A? The code requires both of these things to be true.

This code is working in the test wb I'm using.
View attachment 74951

Yes sir, all in row1 and starts at column A, but i don't include the data in column A because I'm using it for another formula, so stacking will start at column B only.

 

[Max1616]

Also, the code requires the workbook and sheet you're running the code on to be the active workbook/sheet.

 

[Max1616]

Nothing happens at all? Do you get any errors? If not, can you step through the code and hover over "cells(j,i)" does one of the values that should be copied show up in the yellow tool tip?