Macro For Transpose Paste Into New Rows

Biggy · Mar 6, 2016

Hello Excel Community.

Once again I got a problem. This one is similar previous one, but at the same time different.
[TABLE="width: 940"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Original:

	A	B	C	D	E	F	G	H	I	J	K	L	M

T-Shirt
T-Shirt


Polo Shirt	N
Polo Shirt	N
Polo Shirt

<tbody>
[TD="align: center"] 1 [/TD]
[TD="bgcolor: #D9D9D9"] Name [/TD]
[TD="bgcolor: #D9D9D9, align: center"] Color [/TD]
[TD="bgcolor: #D9D9D9, align: center"][/TD]
[TD="bgcolor: #D9D9D9, align: center"][/TD]
[TD="bgcolor: #D9D9D9, align: center"] 8 [/TD]
[TD="bgcolor: #D9D9D9, align: center"] 10 [/TD]
[TD="bgcolor: #D9D9D9, align: center"] 12 [/TD]
[TD="bgcolor: #D9D9D9, align: center"] 14 [/TD]
[TD="bgcolor: #D9D9D9, align: center"] 16 [/TD]
[TD="bgcolor: #D9D9D9, align: center"] 18 [/TD]
[TD="bgcolor: #D9D9D9, align: center"] 20 [/TD]
[TD="bgcolor: #D9D9D9, align: center"] 22 [/TD]
[TD="bgcolor: #D9D9D9, align: center"] Price [/TD]

[TD="align: center"] 2 [/TD]

[TD="align: center"] White [/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] N [/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] $10 [/TD]

[TD="align: center"] 3 [/TD]

[TD="align: center"] Black [/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] $14 [/TD]

[TD="align: center"] 4 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"] 5 [/TD]
[TD="bgcolor: #D9D9D9"] Name [/TD]
[TD="bgcolor: #D9D9D9, align: center"] Color [/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: center"][/TD]
[TD="bgcolor: #D9D9D9, align: center"][/TD]
[TD="bgcolor: #D9D9D9, align: center"] M [/TD]
[TD="bgcolor: #D9D9D9, align: center"] L [/TD]
[TD="bgcolor: #D9D9D9, align: center"] XL [/TD]
[TD="bgcolor: #D9D9D9, align: center"] XXL [/TD]
[TD="bgcolor: #D9D9D9, align: center"] Price [/TD]

[TD="align: center"] 6 [/TD]

[TD="align: center"] Yellow [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"] $12 [/TD]

[TD="align: center"] 7 [/TD]

[TD="align: center"] Orange [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: center"] $12 [/TD]

[TD="align: center"] 8 [/TD]

[TD="align: center"] Black [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"] $16 [/TD]

</tbody>

Should be

A B C D E

<tbody>
[TD="align: center"] 13 [/TD]
[TD="bgcolor: #BFBFBF, align: center"] Name [/TD]
[TD="bgcolor: #BFBFBF, align: center"] Color [/TD]
[TD="bgcolor: #BFBFBF, align: center"] Size [/TD]
[TD="bgcolor: #BFBFBF, align: center"] Available [/TD]
[TD="bgcolor: #BFBFBF, align: center"] Price [/TD]

[TD="align: center"] 14 [/TD]
[TD="bgcolor: #FFFFFF"] T-Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] White [/TD]
[TD="bgcolor: #FFFFFF, align: center"] 8 [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $10 [/TD]

[TD="align: center"] 15 [/TD]
[TD="bgcolor: #FFFFFF"] T-Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] White [/TD]
[TD="bgcolor: #FFFFFF, align: center"] 10 [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $10 [/TD]

[TD="align: center"] 16 [/TD]
[TD="bgcolor: #FFFFFF"] T-Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] White [/TD]
[TD="bgcolor: #FFFFFF, align: center"] 12 [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $10 [/TD]

[TD="align: center"] 17 [/TD]
[TD="bgcolor: #FFFFFF"] T-Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] White [/TD]
[TD="bgcolor: #FFFFFF, align: center"] 14 [/TD]
[TD="bgcolor: #FFFFFF, align: center"] N [/TD]
[TD="bgcolor: #FFFFFF, align: center"] $10 [/TD]

[TD="align: center"] 18 [/TD]
[TD="bgcolor: #FFFFFF"] T-Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] White [/TD]
[TD="bgcolor: #FFFFFF, align: center"] 16 [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $10 [/TD]

[TD="align: center"] 19 [/TD]
[TD="bgcolor: #FFFFFF"] T-Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] White [/TD]
[TD="bgcolor: #FFFFFF, align: center"] 18 [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $10 [/TD]

[TD="align: center"] 20 [/TD]
[TD="bgcolor: #FFFFFF"] T-Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] White [/TD]
[TD="bgcolor: #FFFFFF, align: center"] 20 [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $10 [/TD]

[TD="align: center"] 21 [/TD]
[TD="bgcolor: #FFFFFF"] T-Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] White [/TD]
[TD="bgcolor: #FFFFFF, align: center"] 22 [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $10 [/TD]

[TD="align: center"] 22 [/TD]
[TD="bgcolor: #FFFFFF"] T-Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Black [/TD]
[TD="bgcolor: #FFFFFF, align: center"] 8 [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $14 [/TD]

[TD="align: center"] 23 [/TD]
[TD="bgcolor: #FFFFFF"] T-Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Black [/TD]
[TD="bgcolor: #FFFFFF, align: center"] 10 [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $14 [/TD]

[TD="align: center"] 24 [/TD]
[TD="bgcolor: #FFFFFF"] T-Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Black [/TD]
[TD="bgcolor: #FFFFFF, align: center"] 12 [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $14 [/TD]

[TD="align: center"] 25 [/TD]
[TD="bgcolor: #FFFFFF"] T-Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Black [/TD]
[TD="bgcolor: #FFFFFF, align: center"] 14 [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $14 [/TD]

[TD="align: center"] 26 [/TD]
[TD="bgcolor: #FFFFFF"] T-Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Black [/TD]
[TD="bgcolor: #FFFFFF, align: center"] 16 [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $14 [/TD]

[TD="align: center"] 27 [/TD]
[TD="bgcolor: #FFFFFF"] T-Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Black [/TD]
[TD="bgcolor: #FFFFFF, align: center"] 18 [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $14 [/TD]

[TD="align: center"] 28 [/TD]
[TD="bgcolor: #FFFFFF"] T-Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Black [/TD]
[TD="bgcolor: #FFFFFF, align: center"] 20 [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $14 [/TD]

[TD="align: center"] 29 [/TD]
[TD="bgcolor: #FFFFFF"] T-Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Black [/TD]
[TD="bgcolor: #FFFFFF, align: center"] 22 [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $14 [/TD]

[TD="align: center"] 30 [/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]

[TD="align: center"] 31 [/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]

[TD="align: center"] 32 [/TD]
[TD="bgcolor: #BFBFBF, align: center"] Name [/TD]
[TD="bgcolor: #BFBFBF, align: center"] Color [/TD]
[TD="bgcolor: #BFBFBF, align: center"] Size [/TD]
[TD="bgcolor: #BFBFBF, align: center"] Available [/TD]
[TD="bgcolor: #BFBFBF, align: center"] Price [/TD]

[TD="align: center"] 33 [/TD]
[TD="bgcolor: #FFFFFF"] Polo Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Yellow [/TD]
[TD="bgcolor: #FFFFFF, align: center"] M [/TD]
[TD="bgcolor: #FFFFFF, align: center"] N [/TD]
[TD="bgcolor: #FFFFFF, align: center"] $14 [/TD]

[TD="align: center"] 34 [/TD]
[TD="bgcolor: #FFFFFF"] Polo Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Yellow [/TD]
[TD="bgcolor: #FFFFFF, align: center"] L [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $14 [/TD]

[TD="align: center"] 35 [/TD]
[TD="bgcolor: #FFFFFF"] Polo Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Yellow [/TD]
[TD="bgcolor: #FFFFFF, align: center"] XL [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $14 [/TD]

[TD="align: center"] 36 [/TD]
[TD="bgcolor: #FFFFFF"] Polo Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Yellow [/TD]
[TD="bgcolor: #FFFFFF, align: center"] XXL [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $14 [/TD]

[TD="align: center"] 37 [/TD]
[TD="bgcolor: #FFFFFF"] Polo Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Orange [/TD]
[TD="bgcolor: #FFFFFF, align: center"] M [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $14 [/TD]

[TD="align: center"] 38 [/TD]
[TD="bgcolor: #FFFFFF"] Polo Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Orange [/TD]
[TD="bgcolor: #FFFFFF, align: center"] L [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $14 [/TD]

[TD="align: center"] 39 [/TD]
[TD="bgcolor: #FFFFFF"] Polo Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Orange [/TD]
[TD="bgcolor: #FFFFFF, align: center"] XL [/TD]
[TD="bgcolor: #FFFFFF, align: center"] N [/TD]
[TD="bgcolor: #FFFFFF, align: center"] $14 [/TD]

[TD="align: center"] 40 [/TD]
[TD="bgcolor: #FFFFFF"] Polo Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Orange [/TD]
[TD="bgcolor: #FFFFFF, align: center"] XXL [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $14 [/TD]

[TD="align: center"] 41 [/TD]
[TD="bgcolor: #FFFFFF"] Polo Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Orange [/TD]
[TD="bgcolor: #FFFFFF, align: center"] M [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $16 [/TD]

[TD="align: center"] 42 [/TD]
[TD="bgcolor: #FFFFFF"] Polo Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Orange [/TD]
[TD="bgcolor: #FFFFFF, align: center"] L [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $16
[/TD]

[TD="align: center"] 43 [/TD]
[TD="bgcolor: #FFFFFF"] Polo Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Orange [/TD]
[TD="bgcolor: #FFFFFF, align: center"] XL [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $16 [/TD]

[TD="align: center"] 44 [/TD]
[TD="bgcolor: #FFFFFF"] Polo Shirt [/TD]
[TD="bgcolor: #FFFFFF, align: center"] Orange [/TD]
[TD="bgcolor: #FFFFFF, align: center"] XXL [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] $16 [/TD]

</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.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sincerely,
Biggy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Rick Rothstein · Mar 6, 2016

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

Code:

[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 · Mar 6, 2016

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

Code:

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 · Mar 6, 2016

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 · Mar 6, 2016

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 · Mar 6, 2016

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 · Mar 7, 2016

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

TSH100	T-Shirt
TSH101	T-Shirt


PLSH100	Polo Shirt	N
PLSH101	Polo Shirt	N
PLSH102	Polo Shirt

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #D9D9D9"]Item#[/TD]
[TD="bgcolor: #D9D9D9"]Name[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Color[/TD]
[TD="bgcolor: #D9D9D9, align: center"][/TD]
[TD="bgcolor: #D9D9D9, align: center"][/TD]
[TD="bgcolor: #D9D9D9, align: center"]8[/TD]
[TD="bgcolor: #D9D9D9, align: center"]10[/TD]
[TD="bgcolor: #D9D9D9, align: center"]12[/TD]
[TD="bgcolor: #D9D9D9, align: center"]14[/TD]
[TD="bgcolor: #D9D9D9, align: center"]16[/TD]
[TD="bgcolor: #D9D9D9, align: center"]18[/TD]
[TD="bgcolor: #D9D9D9, align: center"]20[/TD]
[TD="bgcolor: #D9D9D9, align: center"]22[/TD]
[TD="bgcolor: #D9D9D9, align: center"]24[/TD]
[TD="bgcolor: #D9D9D9, align: center"]26[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Price[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]White[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$10,[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]Black[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]$14,[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #D9D9D9"]Item#[/TD]
[TD="bgcolor: #D9D9D9"]Name[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Color[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: center"][/TD]
[TD="bgcolor: #D9D9D9, align: center"][/TD]
[TD="bgcolor: #D9D9D9, align: center"]M[/TD]
[TD="bgcolor: #D9D9D9, align: center"]L[/TD]
[TD="bgcolor: #D9D9D9, align: center"]XL[/TD]
[TD="bgcolor: #D9D9D9, align: center"]XXL[/TD]
[TD="bgcolor: #D9D9D9, align: center"]3XL[/TD]
[TD="bgcolor: #D9D9D9, align: center"]4XL[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Price[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]Yellow[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]$12,[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]Orange[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]$12,[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]Black[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]$15,[/TD]

</tbody>

After

	A	B	C	D	E	F

TSH100
TSH100
TSH100
TSH100
TSH100
TSH100
TSH100
TSH100
TSH100
TSH100
TSH101
TSH101
TSH101
TSH101
TSH101
TSH101
TSH101
TSH101
TSH101
TSH101



PLSH100
PLSH100
PLSH100
PLSH100
PLSH100
PLSH100
PLSH101
PLSH101
PLSH101
PLSH101
PLSH101
PLSH101
PLSH102
PLSH102
PLSH102
PLSH102
PLSH102
PLSH102

<tbody>
[TD="align: center"]11[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Item#[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Name[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Color[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Size[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Available[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Price[/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]10[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]14[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]12[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]15[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]14[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]16[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]16[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]17[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]18[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]18[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]20[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]19[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]22[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]20[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]24[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]21[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]White[/TD]
[TD="bgcolor: #FFFFFF, align: center"]26[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$10,[/TD]

[TD="align: center"]22[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]23[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]10[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]24[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]12[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]25[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]14[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]26[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]16[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]27[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]18[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]28[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]20[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]29[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]22[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]30[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]24[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]31[/TD]

[TD="bgcolor: #FFFFFF"]T-Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]26[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]32[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]

[TD="align: center"]33[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]

[TD="align: center"]34[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Item#[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Name[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Color[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Size[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Available[/TD]
[TD="bgcolor: #BFBFBF, align: center"]Price[/TD]

[TD="align: center"]35[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Yellow[/TD]
[TD="bgcolor: #FFFFFF, align: center"]M[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]36[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Yellow[/TD]
[TD="bgcolor: #FFFFFF, align: center"]L[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]37[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Yellow[/TD]
[TD="bgcolor: #FFFFFF, align: center"]XL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]38[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Yellow[/TD]
[TD="bgcolor: #FFFFFF, align: center"]XXL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]39[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Yellow[/TD]
[TD="bgcolor: #FFFFFF, align: center"]3XL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]40[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Yellow[/TD]
[TD="bgcolor: #FFFFFF, align: center"]4XL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]41[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Orange[/TD]
[TD="bgcolor: #FFFFFF, align: center"]M[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]42[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Orange[/TD]
[TD="bgcolor: #FFFFFF, align: center"]L[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]43[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Orange[/TD]
[TD="bgcolor: #FFFFFF, align: center"]XL[/TD]
[TD="bgcolor: #FFFFFF, align: center"]N[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]44[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Orange[/TD]
[TD="bgcolor: #FFFFFF, align: center"]XXL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]45[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Orange[/TD]
[TD="bgcolor: #FFFFFF, align: center"]3XL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]46[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Orange[/TD]
[TD="bgcolor: #FFFFFF, align: center"]4XL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$14,[/TD]

[TD="align: center"]47[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]M[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$15,[/TD]

[TD="align: center"]48[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]L[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$15,[/TD]

[TD="align: center"]49[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]XL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$15,[/TD]

[TD="align: center"]50[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]XXL[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$15,[/TD]

[TD="align: center"]51[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]3XL[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$15,[/TD]

[TD="align: center"]52[/TD]

[TD="bgcolor: #FFFFFF"]Polo Shirt[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Black[/TD]
[TD="bgcolor: #FFFFFF, align: center"]4XL[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]$15,[/TD]

</tbody>

hiker95 · Mar 7, 2016

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

Code:

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.

Macro For Transpose Paste Into New Rows

Biggy

New Member

Excel Facts

Rick Rothstein

MrExcel MVP

hiker95

Well-known Member

Biggy

New Member

Biggy

New Member

hiker95

Well-known Member

Biggy

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

Macro For Transpose Paste Into New Rows

New Member

Excel Facts

MrExcel MVP

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock