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.

 

[joeypenn]

You can do it with this formula

Thank you this is helpful.

Say I don't know how many cells in each column will have a value and when a blank shows up that is when I want to switch to a new row?

 

[TomHouy]

You can do it with this formula

I am trying to use this formula, but I am not getting the same results. It's only returning the first value from cell A1 - any idea why this may be happening? I'm using Excel 2013 if it's of any importance. Thanks in advance! By the way I've included a copy of my spreadsheet below.

formula-test

 

[TomHouy]

I am trying to use this formula, but I am not getting the same results. It's only returning the first value from cell A1 - any idea why this may be happening? I'm using Excel 2013 if it's of any importance. Thanks in advance! By the way I've included a copy of my spreadsheet below.

formula-test

Update: I figured out what I was doing wrong, I didn't realize I needed to copy the formula down as many cells for the total number of values as a whole. But would there be some way to grab the data as some sort of an array and just dump it via a formula? In other words, if the number of values in each column is going to be constantly changing, I don't know exactly how many cells down I need to drag the formula in column F.

 

[Angioleta]

tnx Yahya,

it works great for trasforming big data bases to specific reporting formats

 

[joeyjoejoejrshabidu]

I prefer a formula approach over VBA.

If you want to go down the rows first then across the columns in your table use this formula.

=INDEX($B$3:$D$6,MOD(ROW()-ROW($F$2)-1,ROWS($B$3:$D$6))+1,INT((ROW()-ROW($F$2)-1)/ROWS($B$3:$D$6))+1)

If you want to go across the columns first then down the rows in your table use this formula.

=INDEX($B$3:$D$6,INT((ROW()-ROW($H$2)-1)/COLUMNS($B$3:$D$6))+1,MOD(ROW()-ROW($H$2)-1,COLUMNS($B$3:$D$6))+1)

See this link for details How To Turn A Table Into A Column Using Formulas.

 

[jsyguy]

I feel that this would be the most useful way of achieving my goal, but can't seem to get over the final stage.

I'm using the following formula on my data, pulling table data row by row into a column.
=INDEX($A$1:$F$9,INT((ROW()-ROW($G$1)-1)/COLUMNS($A$1:$F$9))+1,MOD(ROW()-ROW($G$1)-1,COLUMNS($A$1:$F$9))+1)

My Data looks like this, purely for this example.

	A	B	C	D	E	F
1	R1,C1	R1,C2	R1,C3	R1,C4	R1,C5	R1,C6
2	R2,C1	R2,C2	R2,C3	R2,C4	R2,C5	R2,C6
3	R3,C1	R3,C2	R3,C3	R3,C4	R3,C5	R3,C6
4	R4,C1	R4,C2	R4,C3	R4,C4	R4,C5	R4,C6
5	R5,C1	R5,C2	R5,C3	R5,C4	R5,C5	R5,C6
6	R6,C1	R6,C2	R6,C3	R6,C4	R6,C5	R6,C6
7	R7,C1	R7,C2	R7,C3	R7,C4	R7,C5	R7,C6
8	R8,C1	R8,C2	R8,C3	R8,C4	R8,C5	R8,C6
9	R9,C1	R9,C2	R9,C3	R9,C4	R9,C5	R9,C6

<tbody>
</tbody>

So the formula works across row 1, then row 2 and so on.

R1,C1

R1,C2

R1,C3

R1,C4

R1,C5

R1,C6

R2,C1

R2,C2

R2,C3

R2,C4

R2,C5

R2,C6

R3,C1

R3,C2

R3,C3

R3,C4

R3,C5

R3,C6

<tbody>
</tbody>

What I am looking to do, is in the same format as this functionality, is only return the value from every 3rd cell. For example returning the below result.

R1,C1

R1,C4

R2,C1

R2,C4

R3,C1

R3,C4

<tbody>
</tbody>

Essentially I need to repeat this process in another column to have a start point of B1, and then C1 to return every 3rd value in H + I respectively.

R1,C1	R1,C2	R1,C3
R1,C4	R1,C5	R1,C6
R2,C1	R2,C2	R2,C3
R2,C4	R2,C5	R2,C6
R3,C1	R3,C2	R3,C3
R3,C4	R3,C5	R3,C6

<tbody>
</tbody>

I will need to apply this to a 90 column table, reducing to 3 columns.

Any assistance would be appreciated.

 

[timmytate]

Sankar,

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


Table To Column


Using VBA:

Sheet1

	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

<thead>
</thead><tbody>
</tbody>

Excel 2003



Sheet1

	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

<thead>
</thead><tbody>
</tbody>

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

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

On this code for VBA, how do you adjust so it copies down the column first, then over to next column. So, I would want it to copy all of B, then over to C, ect. This code copy all row 2, b2, c2, d2 ect, then down to b3, c3, d3 ect. I want it to go B2, b3, b4 then c2, c3, c4.