Multiple Columns into one Column

[junkforhr]

Does anyone know how to do this? I have lots of columns that need to be stacked into a single column (I do not want to combine or concantenate)
eg put all data in column B,column C, column D,column F and column E and stack them one under each other into a single column in column A. I have many many columns each with approx 500 rows of cells.All columns are in the one sheet.
Any ideas appreciated.

 

[sanrv1f]

see if this helps

http://www.cpearson.com/EXCEL/ColumnToTable.aspx

 

[ayazgreat]

Try this

Sub MoveAllDataToColumnA()
    Dim i As Long, ws As Worksheet, rngCopy As Range, rngEnd As Range
    Set ws = ActiveSheet
    Do Until ws.Cells(1, 2).Value = ""
        Set rngCopy = ws.Range("B2", ws.Cells(ws.Rows.Count, "B").End(xlUp))
        Set rngEnd = ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1, 0)
        rngEnd.Resize(rngCopy.Rows.Count, 1).Value = rngCopy.Value
        rngCopy.EntireColumn.Delete
    Loop
End Sub

 

[Sandeep Warrier]

see if this helps

http://www.cpearson.com/EXCEL/ColumnToTable.aspx

Sankar,

I think the OP is looking for the opposite...


http://www.cpearson.com/EXCEL/TableToColumn.aspx


Using VBA:

Excel Workbook
	B	C	D	E
1	Col 1	Col 2	Col 3	Col 4
2	ABC	DEF	GHI	JKL
3	MON	PQR	STU	VWX
4	YZA	BCD	EFG	GIJ
5	KLM	NOP	QRS	TUV
6	WXY	ZAB	CDE	FGH
Sheet1

Excel Workbook
	A
1	Single Column
2	ABC
3	DEF
4	GHI
5	JKL
6	MON
7	PQR
8	STU
9	VWX
10	YZA
11	BCD
12	EFG
13	GIJ
14	KLM
15	NOP
16	QRS
17	TUV
18	WXY
19	ZAB
20	CDE
21	FGH
Excel 2003 Sheet1

Excel 2003

Sub TableToColumn()
    Dim Rng As Range, LR As Long, i As Long
    LR = Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
        Set Rng = Range("B" & i, "E" & i) 'Change range to suit needs
        Range("A" & Rows.Count).End(xlUp)(2).Resize(Rng.Count) = Application.WorksheetFunction.Transpose(Rng)
    Next i
End Sub

 

[Yahya]

You can do it with this formula

Excel Workbook
	A	B	C	D	E	F
1	R1, C1	R1, C2	R1, C3	R1, C4		R1, C1
2	R2, C1	R2, C2	R2, C3	R2, C4		R2, C1
3	R3, C1	R3, C2	R3, C3	R3, C4		R3, C1
4	R4, C1	R4, C2	R4, C3	R4, C4		R4, C1
5	R5, C1	R5, C2	R5, C3	R5, C4		R5, C1
6						R1, C2
7						R2, C2
8						R3, C2
9						R4, C2
10						R5, C2
11						R1, C3
12						R2, C3
13						R3, C3
14						R4, C3
15						R5, C3
16						R1, C4
17						R2, C4
18						R3, C4
19						R4, C4
20						R5, C4
...

Cell Formulas
Range	Formula
F1	=OFFSET($A$1, MOD(ROW()-ROW($F$1),ROWS($A$1:$A$5)), TRUNC((ROW()-ROW($F$1))/ROWS($A$1:$A$5)),1,1)

and this formula from www.cpearson.com
Regards

 

[junkforhr]

Thanks Sandeep, works perfectly!

 

[leandurus]

Sankar,

I think the OP is looking for the opposite...

Hey man, thanks! you helped me a lot with 24000 characters !

 

[jlaborde]

Sankar,

I think the OP is looking for the opposite...

A very helpful discussion. Thank you. Now what if I want to reverse this procedure and place all of the numbers BACK into their original columns?

 

[Sandeep Warrier]

Hi jlaborde,

Have a look at the link provided in Post# 2 by sanrv1f. It should serve your purpose.

 

[jlaborde]

Great! Thanks!