Macro For Transpose Paste Into New Rows

[Biggy]

Hello Excel Community.

Once again I got a problem. This one is similar previous one, but at the same time different.

Original: A B C D E F G H I J K L M 1 Name Color 8 10 12 14 16 18 20 22 Price 2 T-Shirt White N $10 3 T-Shirt Black $14 4 5 Name Color M L XL XXL Price 6 Polo Shirt Yellow N $12 7 Polo Shirt Orange N $12 8 Polo Shirt Black $16 <tbody> </tbody> Should be A B C D E 13 Name Color Size Available Price 14 T-Shirt White 8 $10 15 T-Shirt White 10 $10 16 T-Shirt White 12 $10 17 T-Shirt White 14 N $10 18 T-Shirt White 16 $10 19 T-Shirt White 18 $10 20 T-Shirt White 20 $10 21 T-Shirt White 22 $10 22 T-Shirt Black 8 $14 23 T-Shirt Black 10 $14 24 T-Shirt Black 12 $14 25 T-Shirt Black 14 $14 26 T-Shirt Black 16 $14 27 T-Shirt Black 18 $14 28 T-Shirt Black 20 $14 29 T-Shirt Black 22 $14 30 31 32 Name Color Size Available Price 33 Polo Shirt Yellow M N $14 34 Polo Shirt Yellow L $14 35 Polo Shirt Yellow XL $14 36 Polo Shirt Yellow XXL $14 37 Polo Shirt Orange M $14 38 Polo Shirt Orange L $14 39 Polo Shirt Orange XL N $14 40 Polo Shirt Orange XXL $14 41 Polo Shirt Orange M $16 42 Polo Shirt Orange L $16 43 Polo Shirt Orange XL $16 44 Polo Shirt Orange XXL $16 <tbody> </tbody> Here situation ,in my opinion, is lot more difficult, because number columns in which are sizes are moving (but they always finishing with Column next to the price), as well as necessary to copy items availability info to corresponding sizes for each color. Honestly, I don't know if it's even possible, but here are lot of super smart and knowledgeable excel users, so I give it a shot. My guess is that most realistic possibility would be to make macro in which is possible to choose area in which to perform given actions, because I am a realist - it's not possible to automate it completely, because the way it's formatted. Thank You so much and hopefully someone will be able to help with it.

Sincerely, Biggy

<tbody>
</tbody>

---

 

[Rick Rothstein]

Give this macro a try (it outputs the transposed data beneath the existing data to match what you showed in the layout/results example that you posted)...

[table="width: 500"]
[tr]
	[td]Sub TransposeSizeTable()
  Dim X As Long, Cnt As Long, LastRow As Long, NewStart As Long, Ar As Range
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  NewStart = LastRow + 5
  For Each Ar In Range("A1:A" & LastRow).SpecialCells(xlConstants).Areas
    Cells(NewStart, "A").Resize(, 5) = Array("Name", "Color", "Size", "Available", "Price")
    Cnt = Application.CountA(Ar(1).Offset(, 2).Resize(, 10))
    For X = 1 To Ar.Rows.Count - 1
      Cells(NewStart + X, "A").Resize(Cnt, 2).Value = Ar(1).Offset(X).Resize(, 2).Value
      Cells(NewStart + X, "C").Resize(Cnt) = Application.Transpose(Ar(1).Offset(, 12 - Cnt).Resize(, Cnt))
      Cells(NewStart + X, "E").Resize(Cnt).Value = Ar(1).Offset(X, 12).Value
      If X = 1 Then
        Cells(NewStart + X, "D").Resize(Cnt) = Application.Transpose(Ar(1).Offset(X, 12 - Cnt).Resize(, Cnt))
      Else
        Cells(NewStart + X, "D").Resize(Cnt) = Application.Transpose(Ar(1).Offset(X, 12 - Cnt).Resize(, Cnt))
      End If
      NewStart = NewStart + Cnt - 1
    Next
    NewStart = Cells(Rows.Count, "A").End(xlUp).Row + 3
  Next
End Sub[/td]
[/tr]
[/table]

 

[hiker95]

Biggy,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


Here is a macro solution for you to consider that re-uses two arrays in memory, that is based on the raw data structure that you have displayed.

You can change the raw data worksheet name in the macro.

Sample raw data, and, results:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Color			8	10	12	14	16	18	20	22	Price
2	T-Shirt	White						N					$10
3	T-Shirt	Black											$14
4
5	Name	Color							M	L	XL	XXL	Price
6	Polo Shirt	Yellow							N				$12
7	Polo Shirt	Orange									N		$12
8	Polo Shirt	Black											$16
9
10
11
12
13	Name	Color	Size	Available	Price
14	T-Shirt	White	8		$10
15	T-Shirt	White	10		$10
16	T-Shirt	White	12		$10
17	T-Shirt	White	14	N	$10
18	T-Shirt	White	16		$10
19	T-Shirt	White	18		$10
20	T-Shirt	White	20		$10
21	T-Shirt	White	22		$10
22	T-Shirt	Black	8		$14
23	T-Shirt	Black	10		$14
24	T-Shirt	Black	12		$14
25	T-Shirt	Black	14		$14
26	T-Shirt	Black	16		$14
27	T-Shirt	Black	18		$14
28	T-Shirt	Black	20		$14
29	T-Shirt	Black	22		$14
30
31
32	Name	Color	Size	Available	Price
33	Polo Shirt	Yellow	M	N	$12
34	Polo Shirt	Yellow	L		$12
35	Polo Shirt	Yellow	XL		$12
36	Polo Shirt	Yellow	XXL		$12
37	Polo Shirt	Orange	M		$12
38	Polo Shirt	Orange	L		$12
39	Polo Shirt	Orange	XL	N	$12
40	Polo Shirt	Orange	XXL		$12
41	Polo Shirt	Black	M		$16
42	Polo Shirt	Black	L		$16
43	Polo Shirt	Black	XL		$16
44	Polo Shirt	Black	XXL		$16
45
Original

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).

Sub ReorgData()
' hiker95, 03/06/2016, ME926207
Dim a As Variant, i As Long
Dim o As Variant, j As Long
Dim lr As Long, lc As Long
Dim c As Long, sc As Long, nsr As Long, n As Long
Dim Area As Range, sr As Long, er As Long
Application.ScreenUpdating = False
With Sheets("Original")   '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  nsr = lr + 5
  For Each Area In .Range("A1:A" & lr).SpecialCells(xlCellTypeConstants).Areas
    With Area
      sr = .Row
      er = sr + .Rows.Count - 1
      sc = Cells(sr, 2).End(xlToRight).Column
      a = Range(Cells(sr, 1), Cells(er, lc))
      n = (lc - sc) + 1
      ReDim o(1 To n * (.Rows.Count - 1), 1 To 5)
      For i = 2 To UBound(a, 1)
        For c = sc To lc - 1 Step 1
          j = j + 1
          o(j, 1) = a(i, 1): o(j, 2) = a(i, 2)
          o(j, 3) = a(1, c)
          o(j, 4) = a(i, c)
          o(j, 5) = a(i, lc)
        Next c
      Next i
      With Cells(nsr, 1).Resize(, 5)
        .Value = Array("Name", "Color", "Size", "Available", "Price")
        .Font.Bold = True
        .Interior.ThemeColor = xlThemeColorDark1
        .Interior.TintAndShade = -0.249946592608417
      End With
      Cells(nsr, 2).Resize(, 4).HorizontalAlignment = xlCenter
      Cells(nsr + 1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
      Range("B" & nsr + 1 & ":E" & UBound(o, 1)).HorizontalAlignment = xlCenter
      Range("E" & nsr + 1 & ":E" & UBound(o, 1)).NumberFormat = "$#,##0_);[Red]($#,##0)"
      nsr = nsr + UBound(o, 1) + 1
      Erase a: Erase o: j = 0: sc = 0
    End With
  Next Area
  .Columns.AutoFit
End With
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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.

 

[Biggy]

Wow, Just woooow! I can’t believe how fast I got an answer! Thank You soooo much! You are just lifesaver!

Maybe it’s possible to explain how to change it, in case there are more or less columns with a sizes or there is item Code row in front of the name row?

This would be very helpful, so I can use it also in the future in the similar situation not to ask here again.

Thank You again!

All the best,
Tony

 

[Biggy]

Thank you a lot! It worked great! I can’t believe that I got two fixes for this issue!
I am using Windows 10 with Excel 2013 on the PC.
Maybe it’s possible also for this macro to give an example or even explanation how to adapt it if there are more columns for sizes or items code # goes in front of the name? I am just trying to figure out for future similar situations so I don’t have to ask again. I like to understand not just to use it.
Thanx again a lot!!!

Sincerely,
Tony.

 

[hiker95]

Biggy,

Thanks for the feedback.

You are very welcome. Glad we could help.

Maybe it’s possible also for this macro to give an example or even explanation how to adapt it if there are more columns for sizes or items code # goes in front of the name? I am just trying to figure out for future similar situations so I don’t have to ask again.

Can we see more screenshots of your new raw data, and, results?

 

[Biggy]

Hello,
Here are new format (this also works as example, because in the future situation could change). There are added more columns for sizes and in column A now sits "item#"

Before

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Item#	Name	Color			8	10	12	14	16	18	20	22	24	26	Price
2	TSH100	T-Shirt	White						N							$10,
3	TSH101	T-Shirt	Black													$14,
4
5	Item#	Name	Color							M	L	XL	XXL	3XL	4XL	Price
6	PLSH100	Polo Shirt	Yellow							N						$12,
7	PLSH101	Polo Shirt	Orange									N				$12,
8	PLSH102	Polo Shirt	Black													$15,

<tbody>
</tbody>

After

	A	B	C	D	E	F
11	Item#	Name	Color	Size	Available	Price
12	TSH100	T-Shirt	White	8		$10,
13	TSH100	T-Shirt	White	10		$10,
14	TSH100	T-Shirt	White	12		$10,
15	TSH100	T-Shirt	White	14	N	$10,
16	TSH100	T-Shirt	White	16		$10,
17	TSH100	T-Shirt	White	18		$10,
18	TSH100	T-Shirt	White	20		$10,
19	TSH100	T-Shirt	White	22		$10,
20	TSH100	T-Shirt	White	24		$10,
21	TSH100	T-Shirt	White	26		$10,
22	TSH101	T-Shirt	Black	8		$14,
23	TSH101	T-Shirt	Black	10		$14,
24	TSH101	T-Shirt	Black	12		$14,
25	TSH101	T-Shirt	Black	14		$14,
26	TSH101	T-Shirt	Black	16		$14,
27	TSH101	T-Shirt	Black	18		$14,
28	TSH101	T-Shirt	Black	20		$14,
29	TSH101	T-Shirt	Black	22		$14,
30	TSH101	T-Shirt	Black	24		$14,
31	TSH101	T-Shirt	Black	26		$14,
32
33
34	Item#	Name	Color	Size	Available	Price
35	PLSH100	Polo Shirt	Yellow	M	N	$14,
36	PLSH100	Polo Shirt	Yellow	L		$14,
37	PLSH100	Polo Shirt	Yellow	XL		$14,
38	PLSH100	Polo Shirt	Yellow	XXL		$14,
39	PLSH100	Polo Shirt	Yellow	3XL		$14,
40	PLSH100	Polo Shirt	Yellow	4XL		$14,
41	PLSH101	Polo Shirt	Orange	M		$14,
42	PLSH101	Polo Shirt	Orange	L		$14,
43	PLSH101	Polo Shirt	Orange	XL	N	$14,
44	PLSH101	Polo Shirt	Orange	XXL		$14,
45	PLSH101	Polo Shirt	Orange	3XL		$14,
46	PLSH101	Polo Shirt	Orange	4XL		$14,
47	PLSH102	Polo Shirt	Black	M		$15,
48	PLSH102	Polo Shirt	Black	L		$15,
49	PLSH102	Polo Shirt	Black	XL		$15,
50	PLSH102	Polo Shirt	Black	XXL		$15,
51	PLSH102	Polo Shirt	Black	3XL		$15,
52	PLSH102	Polo Shirt	Black	4XL		$15,

<tbody>
</tbody>

 

[hiker95]

Biggy,

Here is a new macro solution for you to consider that re-uses two arrays in memory, that is based on the new raw data structure that you have displayed.

You can change the raw data worksheet name in the macro.

Sample raw data, and, results:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Item#	Name	Color			8	10	12	14	16	18	20	22	24	26	Price
2	TSH100	T-Shirt	White						N							$10,
3	TSH101	T-Shirt	Black													$14,
4
5	Item#	Name	Color							M	L	XL	XXL	3XL	4XL	Price
6	PLSH100	Polo Shirt	Yellow							N						$12,
7	PLSH101	Polo Shirt	Orange									N				$12,
8	PLSH102	Polo Shirt	Black													$15,
9
10
11	Item#	Name	Color	Size	Available	Price
12	TSH100	T-Shirt	White	8		$10,
13	TSH100	T-Shirt	White	10		$10,
14	TSH100	T-Shirt	White	12		$10,
15	TSH100	T-Shirt	White	14	N	$10,
16	TSH100	T-Shirt	White	16		$10,
17	TSH100	T-Shirt	White	18		$10,
18	TSH100	T-Shirt	White	20		$10,
19	TSH100	T-Shirt	White	22		$10,
20	TSH100	T-Shirt	White	24		$10,
21	TSH100	T-Shirt	White	26		$10,
22	TSH101	T-Shirt	Black	8		$14,
23	TSH101	T-Shirt	Black	10		$14,
24	TSH101	T-Shirt	Black	12		$14,
25	TSH101	T-Shirt	Black	14		$14,
26	TSH101	T-Shirt	Black	16		$14,
27	TSH101	T-Shirt	Black	18		$14,
28	TSH101	T-Shirt	Black	20		$14,
29	TSH101	T-Shirt	Black	22		$14,
30	TSH101	T-Shirt	Black	24		$14,
31	TSH101	T-Shirt	Black	26		$14,
32
33
34	Item#	Name	Color	Size	Available	Price
35	PLSH100	Polo Shirt	Yellow	M	N	$12,
36	PLSH100	Polo Shirt	Yellow	L		$12,
37	PLSH100	Polo Shirt	Yellow	XL		$12,
38	PLSH100	Polo Shirt	Yellow	XXL		$12,
39	PLSH100	Polo Shirt	Yellow	3XL		$12,
40	PLSH100	Polo Shirt	Yellow	4XL		$12,
41	PLSH101	Polo Shirt	Orange	M		$12,
42	PLSH101	Polo Shirt	Orange	L		$12,
43	PLSH101	Polo Shirt	Orange	XL	N	$12,
44	PLSH101	Polo Shirt	Orange	XXL		$12,
45	PLSH101	Polo Shirt	Orange	3XL		$12,
46	PLSH101	Polo Shirt	Orange	4XL		$12,
47	PLSH102	Polo Shirt	Black	M		$15,
48	PLSH102	Polo Shirt	Black	L		$15,
49	PLSH102	Polo Shirt	Black	XL		$15,
50	PLSH102	Polo Shirt	Black	XXL		$15,
51	PLSH102	Polo Shirt	Black	3XL		$15,
52	PLSH102	Polo Shirt	Black	4XL		$15,
53
Original

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).

Sub ReorgData_V2()
' hiker95, 03/07/2016, ME926207
Dim a As Variant, i As Long
Dim o As Variant, j As Long
Dim lr As Long, lc As Long
Dim c As Long, sc As Long, nsr As Long, n As Long
Dim Area As Range, sr As Long, er As Long
Application.ScreenUpdating = False
With Sheets("Original")   '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  nsr = lr + 3
  For Each Area In .Range("A1:A" & lr).SpecialCells(xlCellTypeConstants).Areas
    With Area
      sr = .Row
      er = sr + .Rows.Count - 1
      sc = Cells(sr, 3).End(xlToRight).Column
      a = Range(Cells(sr, 1), Cells(er, lc))
      n = (lc - sc) + 1
      ReDim o(1 To n * (.Rows.Count - 1), 1 To 6)
      For i = 2 To UBound(a, 1)
        For c = sc To lc - 1 Step 1
          j = j + 1
          o(j, 1) = a(i, 1): o(j, 2) = a(i, 2): o(j, 3) = a(i, 3)
          o(j, 4) = a(1, c)
          o(j, 5) = a(i, c)
          o(j, 6) = a(i, lc)
        Next c
      Next i
      With Cells(nsr, 1).Resize(, 6)
        .Value = Array("Item#", "Name", "Color", "Size", "Available", "Price")
        .Interior.ThemeColor = xlThemeColorDark1
        .Interior.TintAndShade = -0.249946592608417
      End With
      Cells(nsr, 1).Resize(, 6).HorizontalAlignment = xlCenter
      Cells(nsr + 1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
      Range("C" & nsr + 1 & ":F" & UBound(o, 1)).HorizontalAlignment = xlCenter
      Range("E" & nsr + 1 & ":F" & UBound(o, 1)).NumberFormat = "General"
      nsr = nsr + UBound(o, 1) + 1
      Erase a: Erase o: j = 0: sc = 0
    End With
  Next Area
  .Columns.AutoFit
End With
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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData_V2macro.