Transpose Macro Needed

[turner38]

Hello,

I have a spreadsheet as shown below that has different columns of data organized vertically.
Sheet1

Excel Workbook
	A	B	C	D	E	F
1	YEAR_NUMBER	WEEK_NUMBER	LOCATION_ID	PRODUCTION_METHOD_NUMBER	METRIC_ID	ACTUAL_VALUE
2	2011	21	ALPHA	GB	Input 1	1.000
3	2011	21	ALPHA	GB	Input 2	2.000
4	2011	21	ALPHA	GB	Input 3	3.000
5	2011	21	ALPHA	GB	Input 4	4.000
6	2011	21	ALPHA	GB	Input 5	5.000
7	2011	21	ALPHA	GB	Input 6	6.000
8	2011	21	ALPHA	GB	Input 7	7.000
9	2011	21	ALPHA	GB	Input 8	8.000
10	2011	21	ALPHA	GB	Input 9	9.000
...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	YEAR_NUMBER	WEEK_NUMBER	LOCATION_ID	PRODUCTION_METHOD_NUMBER	Input 1	Input 2	Input 3	Input 4	Input 5	Input 6	Input 7	Input 8	Input 9
2	2011	21	ALPHA	GB	1.000	2.000	3.000	4.000	5.000	6.000	7.000	8.000	9.000
Excel 2007[/B] I am trying to find a macro that will transpose the above data into the format below. I can manually do it with the transpose function but I have about 20,000+ lines of data and it would take a long time to do. Weekly raw transposed

Excel 2007

I am trying to get the data into the format so I can put it in an Access table to run reports with.

Any Help is GREATLY Appreciated!!!!

John

 

[turner38]

Here is what I am starting with:

Excel Workbook
	A	B	C	D	E	F
1	YEAR_NUMBER	WEEK_NUMBER	LOCATION_ID	PRODUCTION_METHOD_NUMBER	METRIC_ID	ACTUAL_VALUE
2	2011	21	ALPHA	GB	Input 1	1.000
3	2011	21	ALPHA	GB	Input 2	2.000
4	2011	21	ALPHA	GB	Input 3	3.000
5	2011	21	ALPHA	GB	Input 4	4.000
6	2011	21	ALPHA	GB	Input 5	5.000
7	2011	21	ALPHA	GB	Input 6	6.000
8	2011	21	ALPHA	GB	Input 7	7.000
9	2011	21	ALPHA	GB	Input 8	8.000
10	2011	21	ALPHA	GB	Input 9	9.000
11	2011	21	ALPHA	GB	Input 10	10.000
12	2011	21	ALPHA	GB	Input 11	11.000
13	2012	21	BETA	RD	Input 1	11.000
14	2012	21	BETA	RD	Input 2	10.000
15	2012	21	BETA	RD	Input 3	9.000
16	2012	21	BETA	RD	Input 4	8.000
17	2012	21	BETA	RD	Input 5	7.000
18	2012	21	BETA	RD	Input 6	6.000
19	2012	21	BETA	RD	Input 7	5.000
20	2012	21	BETA	RD	Input 8	4.000
21	2012	21	BETA	RD	Input 9	3.000
22	2012	21	BETA	RD	Input 10	2.000
23	2012	21	BETA	RD	Input 11	1.000
Sheet1

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	YEAR_NUMBER	WEEK_NUMBER	LOCATION_ID	PRODUCTION_METHOD_NUMBER	Input 1	Input 2	Input 3	Input 4	Input 5	Input 6	Input 7	Input 8	Input 9
Excel 2007 And Here is how I am setting up my sheet 2 headings (Input 10 and Input 11 are there just can't fit them with the HTML Maker) Sheet2

#VALUE!

Excel 2007

The value 11 is missing for line 3 for RD- input 1.

 

[hiker95]

turner38,


Sample worksheets before the macro, and your raw data in Sheet1, already sorted and grouped per your screenshot:

Excel Workbook
	A	B	C	D	E	F
1	YEAR_NUMBER	WEEK_NUMBER	LOCATION_ID	PRODUCTION_METHOD_NUMBER	METRIC_ID	ACTUAL_VALUE
2	2011	21	ALPHA	GB	Input 1	1
3	2011	21	ALPHA	GB	Input 2	2
4	2011	21	ALPHA	GB	Input 3	3
5	2011	21	ALPHA	GB	Input 4	4
6	2011	21	ALPHA	GB	Input 5	5
7	2011	21	ALPHA	GB	Input 6	6
8	2011	21	ALPHA	GB	Input 7	7
9	2011	21	ALPHA	GB	Input 8	8
10	2011	21	ALPHA	GB	Input 9	9
11	2011	21	ALPHA	GB	Input 10	10
12	2011	21	ALPHA	GB	Input 11	11
13	2012	21	BETA	RD	Input 1	11
14	2012	21	BETA	RD	Input 2	10
15	2012	21	BETA	RD	Input 3	9
16	2012	21	BETA	RD	Input 4	8
17	2012	21	BETA	RD	Input 5	7
18	2012	21	BETA	RD	Input 6	6
19	2012	21	BETA	RD	Input 7	5
20	2012	21	BETA	RD	Input 8	4
21	2012	21	BETA	RD	Input 9	3
22	2012	21	BETA	RD	Input 10	2
23	2012	21	BETA	RD	Input 11	1
24
Sheet1

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3
4
Sheet2

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	YEAR_NUMBER	WEEK_NUMBER	LOCATION_ID	PRODUCTION_METHOD_NUMBER	Input 1	Input 2	Input 3	Input 4	Input 5	Input 6	Input 7	Input 8	Input 9	Input 10	Input 11
2	2011	21	ALPHA	GB	1	2	3	4	5	6	7	8	9	10	11
3	2012	21	BETA	RD	11	10	9	8	7	6	5	4	3	2	1
4
Sheet2

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, by highlighting the code and pressing the keys CTRL + C
2. Open your 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 by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 07/08/2011
' http://www.mrexcel.com/forum/showthread.php?t=562425
Dim LR As Long, SR As Long, ER As Long, a As Long, aa As Long, FC As Long
Application.ScreenUpdating = False
Worksheets("Sheet2").UsedRange.Clear
Worksheets("Sheet1").UsedRange.Copy Worksheets("Sheet2").Range("A1")
Worksheets("Sheet2").Activate
LR = Cells(Rows.Count, "A").End(xlUp).Row
With Range("G2:G" & LR)
  .FormulaR1C1 = "=RC[-6]&RC[-5]&RC[-4]&RC[-3]"
  .Value = .Value
End With
Columns("A:D").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("H1"), Unique:=True
LR = Cells(Rows.Count, "H").End(xlUp).Row
With Range("L2:L" & LR)
  .FormulaR1C1 = "=RC[-4]&RC[-3]&RC[-2]&RC[-1]"
  .Value = .Value
End With
Columns(5).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns(13), Unique:=True
LR = Cells(Rows.Count, "M").End(xlUp).Row
With Range("M1").Resize(, LR - 1)
  .Value = Application.Transpose(Range("M2:M" & LR))
  .Font.Bold = True
End With
Range("M2:M" & LR).ClearContents
LR = Cells(Rows.Count, "L").End(xlUp).Row
For a = 2 To LR Step 1
  SR = Application.Match(Range("L" & a), Columns(7), 0)
  ER = Application.Match(Range("L" & a), Columns(7), 1)
  For aa = SR To ER Step 1
    FC = 0
    On Error Resume Next
    FC = Application.Match(Range("E" & aa), Rows(1), 0)
    On Error GoTo 0
    If FC > 0 Then Cells(a, FC).Value = Range("F" & aa).Value
  Next aa
Next a
Columns("A:G").Delete
Columns(5).Delete
ActiveSheet.UsedRange.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Then run the ReorgData macro.

 

[turner38]

Worked Perfectly! Thanks for the help Hiker. I really appreciate it!

 

[hiker95]

turner38,

Thanks for the feedback.

You are very welcome.

Glad I could help.

Come back anytime.

 

[turner38]

Hiker,

It worked perfectly a couple of times then it when I tried running it again it started getting Mismatch errors. Now, it runs with no errors but doesn't pull any of my values it just shows zeros instead. The header and data in columns A through E are correct, but the values in E through O are just zeros now.

Any idea what I did wrong? I have opened a new workbook and tried it several times with no luck.

 

[hiker95]

turner38,

Do any of the cells in your raw data contain formulas?

Can we get one of the workbooks where the macro is not working correctly (with sensative information changed).


See below in my Signature block: You can upload your workbook to Box Net, and provide us with a link to your workbook.

 

[turner38]

Hiker,

I have uploaded my file to box.net. Here is the link:

http://www.box.net/shared/2kjtl3hngeoyyid08j3l

I have inlcuded about 2,000 lines of data in this file. I will be using this macro on about 20,000 lines if possible?

The input tab is where I paste my data (Columns A through F). I then run a macro to cleanse the data (Delete unwanted rows as well as rename the metric id. I then run the transpose macro and the re-configured data is on the Output tab, but it is still missing data values. Am I missing a step?

Also, You will notice I have a button on the Input tab labeled "Transpose Data". Ideally I would like to use this to run the macro instead of having to do ALT F8. Is this possible?

Is there any special rules I need to keep in mind in order for the macro to function properly?

Thanks for your help!

 

[hiker95]

turner38,


Thanks for the workbook.


Sample raw data before your Click Here First button is pressed:

Excel Workbook
	A	B	C	D	E	F
1	YEAR_NUMBER	WEEK_NUMBER	LOCATION_ID	PRODUCTION_METHOD_NUMBER	METRIC_ID	ACTUAL_VALUE
2	2011	22	LU_FG	PROMET1	SP#-1	0.00
3	2011	22	LU_FG	PROMET1	SP#-2	0.00
4	2011	22	LU_FG	PROMET1	EE_1#-1	0.00
5	2011	22	LU_FG	PROMET1	EE_1#-2	0.00
6	2011	22	LU_FG	PROMET1	UH_1#-1	0.00
7	2011	22	LU_FG	PROMET1	UH_1#-2	0.00
8	2011	22	LU_FG	PROMET1	RLE (1)#_1	0.00
9	2011	22	LU_FG	PROMET1	RLE (1)#_2	0.00
10	2011	22	LU_FG	PROMET1	TC#_1	0.00
11	2011	22	LU_FG	PROMET1	TC#_2	0.00
12	2011	22	LU_FG	PROMET1	RE	0.00
13	2011	22	LU_FG	PROMET2	SP#-1	24,830.00
14	2011	22	LU_FG	PROMET2	SP#-2	24,259.00
15	2011	22	LU_FG	PROMET2	EE_1#-1	24,259.00
16	2011	22	LU_FG	PROMET2	EE_1#-2	104,000.00
17	2011	22	LU_FG	PROMET2	UH_1#-1	24,259.00
18	2011	22	LU_FG	PROMET2	UH_1#-2	159.00
19	2011	22	LU_FG	PROMET2	RLE (1)#_1	66.00
20	2011	22	LU_FG	PROMET2	RLE (1)#_2	66.00
21	2011	22	LU_FG	PROMET2	TC#_1	208.00
22	2011	22	LU_FG	PROMET2	TC#_2	66.00
23	2011	22	LU_FG	PROMET2	RE	10,510.00
24	2011	22	LU_FG	PROMET3-PROMET1	SP#-1	0.00
25	2011	22	LU_FG	PROMET3-PROMET1	SP#-2	0.00
26	2011	22	LU_FG	PROMET3-PROMET1	EE_1#-1	0.00
27	2011	22	LU_FG	PROMET3-PROMET1	EE_1#-2	0.00
28	2011	22	LU_FG	PROMET3-PROMET1	UH_1#-1	0.00
29	2011	22	LU_FG	PROMET3-PROMET1	UH_1#-2	0.00
30	2011	22	LU_FG	PROMET3-PROMET1	RLE (1)#_1	0.00
31	2011	22	LU_FG	PROMET3-PROMET1	RLE (1)#_2	0.00
32	2011	22	LU_FG	PROMET3-PROMET1	TC#_1	0.00
33	2011	22	LU_FG	PROMET3-PROMET1	TC#_2	0.00
34	2011	22	LU_FG	PROMET3-PROMET1	RE	0.00
35	2011	22	LU_FG	PROMET3	SP#-1	24830.000
36	2011	22	LU_FG	PROMET3	SP#-2	24259.000
37	2011	22	LU_FG	PROMET3	EE_1#-1	24259.000
38	2011	22	LU_FG	PROMET3	EE_1#-2	104000.000
39	2011	22	LU_FG	PROMET3	UH_1#-1	24259.000
40	2011	22	LU_FG	PROMET3	UH_1#-2	159.000
41	2011	22	LU_FG	PROMET3	RLE (1)#_1	66.000
42	2011	22	LU_FG	PROMET3	RLE (1)#_2	66.000
43	2011	22	LU_FG	PROMET3	TC#_1	208.000
44	2011	22	LU_FG	PROMET3	TC#_2	66.000
45	2011	22	LU_FG	PROMET3	RE	10510.000
Input

Sample raw data after your Click Here First button is pressed:

Excel Workbook
	A	B	C	D	E	F
1	YEAR_NUMBER	WEEK_NUMBER	LOCATION_ID	PRODUCTION_METHOD_NUMBER	METRIC_ID	ACTUAL_VALUE
2	2011	22	LU_FG	PROMET1	Sp 1	0.00
3	2011	22	LU_FG	PROMET1	Sp 2	0.00
4	2011	22	LU_FG	PROMET1	E 1	0.00
5	2011	22	LU_FG	PROMET1	E 2	0.00
6	2011	22	LU_FG	PROMET1	UH 1	0.00
7	2011	22	LU_FG	PROMET1	UH 2	0.00
8	2011	22	LU_FG	PROMET1	RL 1	0.00
9	2011	22	LU_FG	PROMET1	RL 2	0.00
10	2011	22	LU_FG	PROMET1	TC 1	0.00
11	2011	22	LU_FG	PROMET1	TC 2	0.00
12	2011	22	LU_FG	PROMET1	RE	0.00
13	2011	22	LU_FG	PROMET2	Sp 1	24,830.00
14	2011	22	LU_FG	PROMET2	Sp 2	24,259.00
15	2011	22	LU_FG	PROMET2	E 1	24,259.00
16	2011	22	LU_FG	PROMET2	E 2	104,000.00
17	2011	22	LU_FG	PROMET2	UH 1	24,259.00
18	2011	22	LU_FG	PROMET2	UH 2	159.00
19	2011	22	LU_FG	PROMET2	RL 1	66.00
20	2011	22	LU_FG	PROMET2	RL 2	66.00
21	2011	22	LU_FG	PROMET2	TC 1	208.00
22	2011	22	LU_FG	PROMET2	TC 2	66.00
23	2011	22	LU_FG	PROMET2	RE	10,510.00
24	2011	22	LU_FG	PROMET3-PROMET1	Sp 1	0.00
25	2011	22	LU_FG	PROMET3-PROMET1	Sp 2	0.00
26	2011	22	LU_FG	PROMET3-PROMET1	E 1	0.00
27	2011	22	LU_FG	PROMET3-PROMET1	E 2	0.00
28	2011	22	LU_FG	PROMET3-PROMET1	UH 1	0.00
29	2011	22	LU_FG	PROMET3-PROMET1	UH 2	0.00
30	2011	22	LU_FG	PROMET3-PROMET1	RL 1	0.00
31	2011	22	LU_FG	PROMET3-PROMET1	RL 2	0.00
32	2011	22	LU_FG	PROMET3-PROMET1	TC 1	0.00
33	2011	22	LU_FG	PROMET3-PROMET1	TC 2	0.00
34	2011	22	LU_FG	PROMET3-PROMET1	RE	0.00
35	2011	22	LU_FG	PROMET3	Sp 1	24830.000
36	2011	22	LU_FG	PROMET3	Sp 2	24259.000
37	2011	22	LU_FG	PROMET3	E 1	24259.000
38	2011	22	LU_FG	PROMET3	E 2	104000.000
39	2011	22	LU_FG	PROMET3	UH 1	24259.000
40	2011	22	LU_FG	PROMET3	UH 2	159.000
41	2011	22	LU_FG	PROMET3	RL 1	66.000
42	2011	22	LU_FG	PROMET3	RL 2	66.000
43	2011	22	LU_FG	PROMET3	TC 1	208.000
44	2011	22	LU_FG	PROMET3	TC 2	66.000
45	2011	22	LU_FG	PROMET3	RE	10510.000
Input

After the macro in worksheet Output:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	YEAR_NUMBER	WEEK_NUMBER	LOCATION_ID	PRODUCTION_METHOD_NUMBER	Sp 1	Sp 2	E 1	E 2	UH 1	UH 2	RL 1	RL 2	TC 1	TC 2	RE
2	2011	22	LU_FG	PROMET1	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
3	2011	22	LU_FG	PROMET2	24,830.00	24,259.00	24,259.00	104,000.00	24,259.00	159.00	66.00	66.00	208.00	66.00	10,510.00
4	2011	22	LU_FG	PROMET3-PROMET1	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
5	2011	22	LU_FG	PROMET3	24,830.00	24,259.00	24,259.00	104,000.00	24,259.00	159.00	66.00	66.00	208.00	66.00	10,510.00
Output

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, by highlighting the code and pressing the keys CTRL + C
2. Open your 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 by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgDataV2()
' hiker95, 07/09/2011
' http://www.mrexcel.com/forum/showthread.php?t=562425
Dim LR As Long, LR2 As Long, SR As Long, ER As Long, a As Long, aa As Long, FC As Long, LC As Long
Application.ScreenUpdating = False
Worksheets("Output").UsedRange.Clear
Worksheets("Input").UsedRange.Copy Worksheets("Output").Range("A1")
Worksheets("Output").Activate
LR = Cells(Rows.count, "A").End(xlUp).Row
With Range("G2:G" & LR)
  .FormulaR1C1 = "=RC[-6]&RC[-5]&RC[-4]&RC[-3]"
  .Value = .Value
End With
Columns("A:D").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("H1"), Unique:=True
LR = Cells(Rows.count, "H").End(xlUp).Row
With Range("L2:L" & LR)
  .FormulaR1C1 = "=RC[-4]&RC[-3]&RC[-2]&RC[-1]"
  .Value = .Value
End With
LR = Cells(Rows.count, "L").End(xlUp).Row
With Range("M2:M" & LR)
  .FormulaR1C1 = "=MATCH(RC[-1],C[-6],0)"
  .Value = .Value
End With
With Range("N2:N" & LR - 1)
  .FormulaR1C1 = "=R[1]C[-1]-1"
  .Value = .Value
End With
LR2 = Cells(Rows.count, "G").End(xlUp).Row
Range("N" & LR) = LR2
Columns(5).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns(15), Unique:=True
LR = Cells(Rows.count, "O").End(xlUp).Row
With Range("O1").Resize(, LR - 1)
  .Value = Application.Transpose(Range("O2:O" & LR))
  .Font.Bold = True
End With
Range("O2:O" & LR).ClearContents
LR = Cells(Rows.count, "L").End(xlUp).Row
For a = 2 To LR Step 1
  SR = Range("M" & a)
  ER = Range("N" & a)
  For aa = SR To ER Step 1
    FC = 0
    On Error Resume Next
    FC = Application.Match(Range("E" & aa), Rows(1), 0)
    On Error GoTo 0
    If FC > 0 Then Cells(a, FC).Value = Range("F" & aa).Value
  Next aa
Next a
Columns("A:G").Delete
Columns("E:G").Delete
LR = Cells(Rows.count, "A").End(xlUp).Row
LC = Cells(1, Columns.count).End(xlToLeft).Column
Range(Cells(2, 5), Cells(LR, LC)).NumberFormat = "#,##0.00"
ActiveSheet.UsedRange.Columns.AutoFit
Application.ScreenUpdating = True
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 ReorgDataV2 macro.


You will have to assign the macro to the Transpose Data button.