Traspose each row every 5 rows (of 15 cells) into one column

[Nataly92]

I am using Microsoft Office Excel 2007 and Windows 7.
I have a lot of data (1-4 thousands of rows) where each column represents a year for 15 years. Every set of 5 rows are different data for a company and the values for every year of each data are under the representative column.

I want to transpose the values for every year, of one data at a time, into one column. Then do the same for the next data.

For Example I have something like this:

	1998	1999	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012
comp.1data1	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
comp.1data2	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
comp.1data3	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45
comp.1data4	46	47	48	49	50	51	52	53	54	55	56	57	58	59	60
comp.1data5	61	62	63	64	65	66	67	68	69	70	71	72	73	74	75
comp.2data1	76	77	78	79	80	81	82	83	84	85	86	87	88	89	90
comp.2data2	91	92	93	94	95	96	97	98	99	100	101	102	103	104	105
comp.2data3	106	107	108	109	110	111	112	113	114	115	116	117	118	119	120
comp.2data4	121	122	123	124	125	126	127	128	129	130	131	132	133	134	135
comp.2data5	136	137	138	139	140	141	142	143	144	145	146	147	148	149	150
comp.3data1	151	152	153	154	155	156	157	158	159	160	161	162	163	164	165
comp.3data2	166	167	168	169	170	171	172	173	174	175	176	177	178	179	180
comp.3data3	181	182	183	184	185	186	187	188	189	190	191	192	193	194	195
comp.3data4	196	197	198	199	200	201	202	203	204	205	206	207	208	209	210
comp.3data5	211	212	213	214	215	216	217	218	219	220	221	222	223	224	225

<tbody>
</tbody>

And I want to make it into like this:

data1	data2	data3	data4	data5
1	16	31	46	61
2	17	32	47	62
3	18	33	48	63
4	19	34	49	64
5	20	35	50	65
6	21	36	51	66
7	22	37	52	67
8	23	38	53	68
9	24	39	54	69
10	25	40	55	70
11	26	41	56	71
12	27	42	57	72
13	28	43	58	73
14	29	44	59	74
15	30	45	60	75
76	91	106	121	136
77	92	107	122	137
78	93	108	123	138
79	94	109	124	139
80	95	110	125	140
81	96	111	126	141
82	97	112	127	142
83	98	113	128	143
84	99	114	129	144
85	100	115	130	145
86	101	116	131	146
87	102	117	132	147
88	103	118	133	148
89	104	119	134	149
90	105	120	135	150
151	166	181	196	211
152	167	182	197	212
153	168	183	198	213
154	169	184	199	214
155	170	185	200	215
156	171	186	201	216
157	172	187	202	217
158	173	188	203	218
159	174	189	204	219
160	175	190	205	220
161	176	191	206	221
162	177	192	207	222
163	178	193	208	223
164	179	194	209	224
165	180	195	210	225

<tbody>
</tbody>

Sorry for the big example. If there is a way to do this for one data (i.e. data1) it will be easy to do it for the other 4. I also know how to create a macro from the Developer Tab if it is easier to create a vba code. A formula of course is ok too. I tried for hours to find a way to do this but I couldn't...
Thank you very much for your help,
Nataly.

 

[Rick Rothstein]

Run this macro with the data sheet as the active sheet and change the output sheet name (hightlighted in red) to whatever sheet name (other than your data sheet) that you want the transposed data to go to....

Sub TransposeData()
  Dim X As Long, R As Long
  R = 2
  With Sheets("Sheet2")
    .Range("A1:E1").Value = WorksheetFunction.Transpose(Range("A2:A6").Value)
    For X = 2 To Cells(Rows.Count, "A").End(xlUp).Row Step 5
      .Cells(R, 1).Resize(15, 5) = WorksheetFunction.Transpose(Cells(X, "B").Resize(5, 15).Value)
      R = R + 5
    Next
  End With
End Sub

 

[Nataly92]

I tried the code but there were a couple of problems which I fixed them in a way. The traspose was made only for the 5 first years. I thought that because the rows for each company were 5 and the years were 15, that the last 10 years weren't copied or they were overcopied by the next company's data. So I used a macro that adds 10 rows every 5 rows, altered the code you provided to fit the new form of my archive and it worked just fine.
Thank you very much! If you hadn't helped me I would have been stuck there doing this manualy...

 

[Rick Rothstein]

I tried the code but there were a couple of problems which I fixed them in a way. The traspose was made only for the 5 first years. I thought that because the rows for each company were 5 and the years were 15, that the last 10 years weren't copied or they were overcopied by the next company's data. So I used a macro that adds 10 rows every 5 rows, altered the code you provided to fit the new form of my archive and it worked just fine.
Thank you very much! If you hadn't helped me I would have been stuck there doing this manualy...

I am glad you got it working, but I am a little confused. I copy/pasted the data you included in your original message into a worksheet (with the first header cell going to cell A1) and then, when I run the macro I posted, it outputs the exact table you showed that you wanted from that data... are you saying it is not doing that for you?

 

[hiker95]

Nataly92,

Welcome to the MrExcel forum.

With your raw data in worksheet Sheet1, the output you are looking for will be in a new worksheet Results.

Sample raw data in worksheet Sheet1:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		1998	1999	2000	2001	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012
2	comp.1data1	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
3	comp.1data2	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
4	comp.1data3	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45
5	comp.1data4	46	47	48	49	50	51	52	53	54	55	56	57	58	59	60
6	comp.1data5	61	62	63	64	65	66	67	68	69	70	71	72	73	74	75
7	comp.2data1	76	77	78	79	80	81	82	83	84	85	86	87	88	89	90
8	comp.2data2	91	92	93	94	95	96	97	98	99	100	101	102	103	104	105
9	comp.2data3	106	107	108	109	110	111	112	113	114	115	116	117	118	119	120
10	comp.2data4	121	122	123	124	125	126	127	128	129	130	131	132	133	134	135
11	comp.2data5	136	137	138	139	140	141	142	143	144	145	146	147	148	149	150
12	comp.3data1	151	152	153	154	155	156	157	158	159	160	161	162	163	164	165
13	comp.3data2	166	167	168	169	170	171	172	173	174	175	176	177	178	179	180
14	comp.3data3	181	182	183	184	185	186	187	188	189	190	191	192	193	194	195
15	comp.3data4	196	197	198	199	200	201	202	203	204	205	206	207	208	209	210
16	comp.3data5	211	212	213	214	215	216	217	218	219	220	221	222	223	224	225
17
Sheet1

After the macro in a new worksheet Results:

Excel 2007
	A	B	C	D	E
1	data1	data2	data3	data4	data5
2	1	16	31	46	61
3	2	17	32	47	62
4	3	18	33	48	63
5	4	19	34	49	64
6	5	20	35	50	65
7	6	21	36	51	66
8	7	22	37	52	67
9	8	23	38	53	68
10	9	24	39	54	69
11	10	25	40	55	70
12	11	26	41	56	71
13	12	27	42	57	72
14	13	28	43	58	73
15	14	29	44	59	74
16	15	30	45	60	75
17	76	91	106	121	136
18	77	92	107	122	137
19	78	93	108	123	138
20	79	94	109	124	139
21	80	95	110	125	140
22	81	96	111	126	141
23	82	97	112	127	142
24	83	98	113	128	143
25	84	99	114	129	144
26	85	100	115	130	145
27	86	101	116	131	146
28	87	102	117	132	147
29	88	103	118	133	148
30	89	104	119	134	149
31	90	105	120	135	150
32	151	166	181	196	211
33	152	167	182	197	212
34	153	168	183	198	213
35	154	169	184	199	214
36	155	170	185	200	215
37	156	171	186	201	216
38	157	172	187	202	217
39	158	173	188	203	218
40	159	174	189	204	219
41	160	175	190	205	220
42	161	176	191	206	221
43	162	177	192	207	222
44	163	178	193	208	223
45	164	179	194	209	224
46	165	180	195	210	225
47
Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 10/19/2013
' http://www.mrexcel.com/forum/excel-questions/733822-traspose-each-row-every-5-rows-15-cells-into-one-column.html
Dim r As Long, lr As Long, lc As Long, nr As Long
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=Sheets("Sheet1")).Name = "Results"
With Sheets("Results")
  .UsedRange.ClearContents
  .Cells(1).Resize(, 5).Value = [{"data1","data2","data3","data4","data5"}]
End With
With Sheets("Sheet1")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  For r = 2 To lr Step 5
    nr = Sheets("Results").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    Sheets("Results").Range("A" & nr).Resize(lc - 1, 5).Value = Application.Transpose(.Range(.Cells(r, 2), .Cells(r + 4, lc)))
  Next r
End With
With Sheets("Results")
  .Columns.AutoFit
  .Activate
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

 

[markmzz]

I am glad you got it working, but I am a little confused. I copy/pasted the data you included in your original message into a worksheet (with the first header cell going to cell A1) and then, when I run the macro I posted, it outputs the exact table you showed that you wanted from that data... are you saying it is not doing that for you?

Hi Rick,

Here is what I got with your code (in yellow):

comp.1data1	comp.1data2	comp.1data3	comp.1data4	comp.1data5	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012
1	16	31	46	61	5	6	7	8	9	10	11	12	13	14	15
2	17	32	47	62	20	21	22	23	24	25	26	27	28	29	30
3	18	33	48	63	35	36	37	38	39	40	41	42	43	44	45
4	19	34	49	64	50	51	52	53	54	55	56	57	58	59	60
5	20	35	50	65	65	66	67	68	69	70	71	72	73	74	75
21	22	23	24	25	80	81	82	83	84	85	86	87	88	89	90
36	37	38	39	40	95	96	97	98	99	100	101	102	103	104	105
51	52	53	54	55	110	111	112	113	114	115	116	117	118	119	120
66	67	68	69	70	125	126	127	128	129	130	131	132	133	134	135
80	95	110	125	140	140	141	142	143	144	145	146	147	148	149	150
96	97	98	99	100	155	156	157	158	159	160	161	162	163	164	165
111	112	113	114	115	170	171	172	173	174	175	176	177	178	179	180
126	127	128	129	130	185	186	187	188	189	190	191	192	193	194	195
141	142	143	144	145	200	201	202	203	204	205	206	207	208	209	210
155	170	185	200	215	215	216	217	218	219	220	221	222	223	224	225
156	171	186	201	216
157	172	187	202	217
158	173	188	203	218
159	174	189	204	219
160	175	190	205	220
161	176	191	206	221
162	177	192	207	222
163	178	193	208	223
164	179	194	209	224
165	180	195	210	225

<tbody>
</tbody>

I did a small modification (in red).

Sub TransposeData_mk()
  Dim X As Long, R As Long
  R = 2
  With Sheets("Sheet2")
    .Range([COLOR="#FF0000"]"R1:V1"[/COLOR]).Value = WorksheetFunction.Transpose(Range("A2:A6").Value)
    For X = 2 To .Cells(Rows.Count, "A").End(xlUp).Row Step 5
        .Cells(R, "R").Resize(15, 5) = WorksheetFunction.Transpose(Cells(X, "B").Resize(5, 15).Value)
        R = R + [COLOR="#FF0000"]15[/COLOR]
    Next
  End With
End Sub

I think it's ok now.

Markmzz

 

[Rick Rothstein]

Hi Rick,

Here is what I got with your code (in yellow):

comp.1data1	comp.1data2	comp.1data3	comp.1data4	comp.1data5	2002	2003	2004	2005	2006	2007	2008	2009	2010	2011	2012
1	16	31	46	61	5	6	7	8	9	10	11	12	13	14	15
2	17	32	47	62	20	21	22	23	24	25	26	27	28	29	30
3	18	33	48	63	35	36	37	38	39	40	41	42	43	44	45
4	19	34	49	64	50	51	52	53	54	55	56	57	58	59	60
5	20	35	50	65	65	66	67	68	69	70	71	72	73	74	75
21	22	23	24	25	80	81	82	83	84	85	86	87	88	89	90
36	37	38	39	40	95	96	97	98	99	100	101	102	103	104	105
51	52	53	54	55	110	111	112	113	114	115	116	117	118	119	120
66	67	68	69	70	125	126	127	128	129	130	131	132	133	134	135
80	95	110	125	140	140	141	142	143	144	145	146	147	148	149	150
96	97	98	99	100	155	156	157	158	159	160	161	162	163	164	165
111	112	113	114	115	170	171	172	173	174	175	176	177	178	179	180
126	127	128	129	130	185	186	187	188	189	190	191	192	193	194	195
141	142	143	144	145	200	201	202	203	204	205	206	207	208	209	210
155	170	185	200	215	215	216	217	218	219	220	221	222	223	224	225
156	171	186	201	216
157	172	187	202	217
158	173	188	203	218
159	174	189	204	219
160	175	190	205	220
161	176	191	206	221
162	177	192	207	222
163	178	193	208	223
164	179	194	209	224
165	180	195	210	225

<TBODY>
</TBODY>

I did a small modification (in red).

Sub TransposeData_mk()
  Dim X As Long, R As Long
  R = 2
  With Sheets("Sheet2")
    .Range([COLOR=#000000]"R1:V1"[/COLOR]).Value = WorksheetFunction.Transpose(Range("A2:A6").Value)
    For X = 2 To .Cells(Rows.Count, "A").End(xlUp).Row Step 5
        .Cells(R, [B][COLOR=#b22222]"R"[/COLOR][/B]).Resize(15, 5) = WorksheetFunction.Transpose(Cells(X, "B").Resize(5, 15).Value)
        R = R + [COLOR=#0000ff][B]15[/B][/COLOR]
    Next
  End With
End Sub

You also changed the text I highlighted in red as well. But why did you change the 5 to 15 (highlighted in blue)? That would not match what the OP posted she wanted originally. As for the yellow highlighted part.... isn't that what the OP indicated she wanted in her original message?

 

[markmzz]

You also changed the text I highlighted in red as well. But why did you change the 5 to 15 (highlighted in blue)? That would not match what the OP posted she wanted originally. As for the yellow highlighted part.... isn't that what the OP indicated she wanted in her original message?

1) Thanks. I forgot the "R" in Cells(R, "R").

2) Before, the data have 15 columns, then, after the use of the function TRANSPOSE, we have 15 rows. So you must to add 15 rows and not 5 rows for the next step. Look at the second table of the post #1.

Markmzz

 

[Rick Rothstein]

2) Before, the data have 15 columns, then, after the use of the function TRANSPOSE, we have 15 rows. So you must to add 15 rows and not 5 rows for the next step. Look at the second table of the post #1.

You are right... a mental lapse on my part (I only got about 3 hours sleep last night and it looks like that may be showing in my posts). Thanks for noting that for me.

 

[markmzz]

You are right... a mental lapse on my part (I only got about 3 hours sleep last night and it looks like that may be showing in my posts). Thanks for noting that for me.

You're welcome.

And sometimes, we all make mistakes, especially when we're tired.

Markmzz