Convert columns into duplication of rows

[rcs]

Dear all,

I have an example of a simple version of a table that I need to convert to different table structure.

Can anybody please help me with this.

I need to convert the following table...
[TABLE="class: grid, width: 200, align: left"]
<TBODY>[TR]
[TD]Dep
[/TD]
[TD]Type
[/TD]
[TD]2010
[/TD]
[TD]2011
[/TD]
[TD]2012
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]X
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]Y
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]X
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]Y
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[/TR]
</TBODY>[/TABLE]








into the following structure.
[TABLE="class: grid, width: 300, align: left"]
<TBODY>[TR]
[TD]Dep
[/TD]
[TD]Type
[/TD]
[TD]Year
[/TD]
[TD]Val
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]X
[/TD]
[TD]2010
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]X
[/TD]
[TD]2011
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]X
[/TD]
[TD]2012
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]Y
[/TD]
[TD]2010
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]Y
[/TD]
[TD]2011
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]Y
[/TD]
[TD]2012
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]X
[/TD]
[TD]2010
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]X
[/TD]
[TD]2011
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]X
[/TD]
[TD]2012
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]Y
[/TD]
[TD]2010
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]Y
[/TD]
[TD]2011
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]Y
[/TD]
[TD]2012
[/TD]
[TD]11
[/TD]
[/TR]
</TBODY>[/TABLE]





















Thank you in advance.

 

[hiker95]

rcs,

Welcome to the MrExcel forum.


Sample raw data:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Dep	Type	2010	2011	2012
2	A	X		1	2
3	A	Y	3	4	5
4	B	X	6	7	8
5	B	Y	9	10	11
6
7
8
9
10
11
12
13
14
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Dep	Type	2010	2011	2012			Dep	Type	Year	Val
2	A	X		1	2			A	X	2010
3	A	Y	3	4	5			A	X	2011	1
4	B	X	6	7	8			A	X	2012	2
5	B	Y	9	10	11			A	Y	2010	3
6								A	Y	2011	4
7								A	Y	2012	5
8								B	X	2010	6
9								B	X	2011	7
10								B	X	2012	8
11								B	Y	2010	9
12								B	Y	2011	10
13								B	Y	2012	11
14
Sheet1

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, 01/15/2013
' http://www.mrexcel.com/forum/excel-questions/679465-convert-columns-into-duplication-rows.html
Dim i As Variant, o As Variant, n As Long
Dim r As Long, rr As Long, lr As Long, c As Long, lc As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
i = Range(Cells(1, 1), Cells(lr, lc))
n = ((lc - 2) * (lr - 1)) + 1
ReDim o(1 To n, 1 To 4)
rr = 1
o(rr, 1) = "Dep"
o(rr, 2) = "Type"
o(rr, 3) = "Year"
o(rr, 4) = "Val"
For r = 2 To UBound(i, 1)
  For c = 3 To lc
    rr = rr + 1
    o(rr, 1) = i(r, 1)
    o(rr, 2) = i(r, 2)
    o(rr, 3) = i(1, c)
    o(rr, 4) = i(r, c)
  Next c
Next r
Cells(1, lc + 3).Resize(UBound(o, 1), UBound(o, 2)) = o
Cells(1, lc + 3).Resize(, UBound(o, 2)).Font.Bold = 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 ReorgData macro.

 

[hiker95]

rcs,

The above macro should also run correctly for more years in row 1 going to the right.

 

[rcs]

Hi hiker95,

Great and thanks for your prompt reply. It looks good and I will have a look and try to run it on my data.

Kind Regards,
rcs

 

[hiker95]

rcs,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.

 

[rcs]

hiker95,

Thanks a lot. Worked like a charm.

Performed well on 3000 rows and around 50 columns.
The result is a table with around 180.000 rows.

Thank you.

KR,
rcs

 

[hiker95]

rcs,

You are very welcome. Glad I could help.

Thanks for the feedback.

Come back anytime.