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]

Here's an example of the tool tip:

 

[usui]

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?
View attachment 74953

Yes nothing happen after run and code is good no yellow highlight

 

[usui]

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.

Thank you for this formula sir, it worked great. Im now need sa vba code this as well and i hope I can get one

 

[jolivanes]

A simple, easy to adapt macro based on Post #1
No change needed if the amount of columns change.

Sub Maybe()
Dim lr As Long, lc As Long, i As Long
lr = Cells(Rows.Count, 2).End(xlUp).Row
lc = Cells(2, Columns.Count).End(xlToLeft).Column
    For i = 2 To lc
        Cells(Rows.Count, lc + 2).End(xlUp).Offset(1).Resize(lr - 1).Value = Cells(2, i).Resize(lr - 1).Value
    Next i
End Sub

 

[usui]

A simple, easy to adapt macro based on Post #1
No change needed if the amount of columns change.

Sub Maybe()
Dim lr As Long, lc As Long, i As Long
lr = Cells(Rows.Count, 2).End(xlUp).Row
lc = Cells(2, Columns.Count).End(xlToLeft).Column
    For i = 2 To lc
        Cells(Rows.Count, lc + 2).End(xlUp).Offset(1).Resize(lr - 1).Value = Cells(2, i).Resize(lr - 1).Value
    Next i
End Sub

Nothing happen as well sir, i think the code did not run after clicking..tried multiple times already

 

[usui]

Here's an example of the tool tip:
View attachment 74954

Hi Sir, I tried again the 2nd code, it's now working but the problem is the results are showing on Column H row 52, I don't know why its there, I just notice it just now upon scrolling down. sory, is there a way we can move it up to column H2 instead?

 

[usui]

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.

Hi Sir, how to change the array? I would like to add 2 more columns (E & F) to make it 5 columns, it's pop up message you can't change part of the array?..can you help me?

 

[Max1616]

Hi Sir, I tried again the 2nd code, it's now working but the problem is the results are showing on Column H row 52, I don't know why its there, I just notice it just now upon scrolling down. sory, is there a way we can move it up to column H2 instead?

The code I created adds the values to the last value present in column H. If there's any data already in column H prior to running the code, it will add it to the bottom.

If you'd like, I can hardcode it so it always starts in row 2. Would that fix your issue?

 

[usui]

The code I created adds the values to the last value present in column H. If there's any data already in column H prior to running the code, it will add it to the bottom.

If you'd like, I can hardcode it so it always starts in row 2. Would that fix your issue?

Yes please, the only data present in column H is the header( Result here). If you can move it to H2 that you be very great sir.

 

[Eric W]

Hi Sir, how to change the array? I would like to add 2 more columns (E & F) to make it 5 columns, it's pop up message you can't change part of the array?..can you help me?

Just change the ranges in the formula (whichever one you want). For example:

=IF(ROWS(H$2:H2)>COUNTA($B$2:$D$20),"",INDEX($B$2:$D$20,MOD(ROWS(H$2:H2)-1,COUNTA($A$2:$A$20))+1,INT((ROWS(H$2:H2)-1)/COUNTA($A$2:$A$20))+1))

or

=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)))

Change those to $B$2:$F$100, or whatever you require. If your columns have more data, you may need to change the $20 reference as well in several places. If you have over 1000 rows per column, you'll need to change the 1000's in the second formula to something bigger, like 100000.