Transpose Large Data Help

devint · Dec 17, 2020

Morning,

Hoping someone might have knowledge that may help me. I have to run a data pull every month. It comes in a certain format, and I need to transpose it into another format. I understand the transpose part, but only for a single set of data. My data is about 6000 rows long.

This is the format the data comes in:

G80012
ASHGREY	S	M	L	XL	2XL	3XL	4XL	5XL
RegularPrice	$2.24	$2.24	$2.24	$2.24	$4.43	$6.03	$5.98	$5.99
SpecialPrice	$1.92	$1.92	$1.92	$1.92
Total-Inventory	27322	4868	16127	2190	78	0	10	11
AZALEA	S	M	L	XL	2XL	3XL	4XL	5XL
RegularPrice	$2.32	$2.32	$2.32	$2.32	$4.91	$6.34	$6.28	$6.28
SpecialPrice	$2.03	$2.03	$2.03	$2.03
Total-Inventory	1646	2000	1355	436	427	70	0	0
BLACK	S	M	L	XL	2XL	3XL	4XL	5XL
RegularPrice	$2.32	$2.32	$2.32	$2.32	$4.91	$6.34	$6.28	$6.28
SpecialPrice	$2.03	$2.03	$2.03	$2.03
Total-Inventory	35935	56924	67178	28203	10723	5075	144	1006

My ideal end result would look like this:

Style	Color	Size	RegularPrice	SpecialPrice	Total-Inventory
G80012	ASHGREY	S	$2.24	$1.92	27322
G80012	ASHGREY	M	$2.24	$1.92	4868
G80012	ASHGREY	L	$2.24	$1.92	16127
G80012	ASHGREY	XL	$2.24	$1.92	2190
G80012	ASHGREY	2XL	$4.43		78
G80012	ASHGREY	3XL	$6.03		0
G80012	ASHGREY	4XL	$5.98		10
G80012	ASHGREY	5XL	$5.99		11
G80012	AZALEA	S	$2.32	$2.03	1646
G80012	AZALEA	M	$2.32	$2.03	2000
G80012	AZALEA	L	$2.32	$2.03	1355
G80012	AZALEA	XL	$2.32	$2.03	436
G80012	AZALEA	2XL	$4.91		427
G80012	AZALEA	3XL	$6.34		70
G80012	AZALEA	4XL	$6.28		0
G80012	AZALEA	5XL	$6.28		0
G80012	BLACK	S	$2.32	$2.03	35935
G80012	BLACK	M	$2.32	$2.03	56924
G80012	BLACK	L	$2.32	$2.03	67178
G80012	BLACK	XL	$2.32	$2.03	28203
G80012	BLACK	2XL	$4.91		10723
G80012	BLACK	3XL	$6.34		5075
G80012	BLACK	4XL	$6.28		144
G80012	BLACK	5XL	$6.28		1006

I can do a transpose and manually copy/paste each 'color', but with 150 colors this would take me a long time. Was hoping there was a formula or macro that can achieve this with a few strokes.

This is beyond my knowledge of excel, so hoping I can find help here.

Thanks,

Peter_SSs · Dec 17, 2020

Welcome to the MrExcel board!

For the future, I suggest that you investigate XL2BB for providing sample data & expected results.

See if this macro is headed in the right direction. Test with a copy of your data.

VBA Code:

Sub Rearrange()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, r As Long, uba1 As Long, uba2 As Long
  Dim sStyle As String, sColor As String
  
  a = Range("A1").CurrentRegion.Value
  uba1 = UBound(a, 1)
  uba2 = UBound(a, 2)
  ReDim b(1 To (uba2 - 1) * (uba1 - 1) / 4, 1 To 6)
  sStyle = a(1, 1)
  For i = 2 To uba1 Step 4
    sColor = a(i, 1)
    For j = 2 To uba2
      r = r + 1
      b(r, 1) = sStyle
      b(r, 2) = sColor
      For k = 0 To 3
        b(r, 3 + k) = a(i + k, j)
      Next k
    Next j
  Next i
  Cells(1, uba2 + 2).Resize(, 6).Value = Split("Style|Color|Size|RegularPrice|SpecialPrice|Total-Inventory", "|")
  Cells(2, uba2 + 2).Resize(UBound(b, 1), 6).Value = b
End Sub

Here is my sample data and results

devint.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

G80012

Style

Color

Size

RegularPrice

SpecialPrice

Total-Inventory

2

ASHGREY

S

M

L

XL

2XL

3XL

4XL

5XL

G80012

ASHGREY

S

$2.24

$1.92

27322

3

RegularPrice

$2.24

$4.43

$6.03

$5.98

$5.99

G80012

ASHGREY

M

$2.24

$1.92

4868

4

SpecialPrice

$1.92

G80012

ASHGREY

L

$2.24

$1.92

16127

5

Total-Inventory

27322

4868

16127

2190

78

0

10

11

G80012

ASHGREY

XL

$2.24

$1.92

2190

6

AZALEA

S

M

L

XL

2XL

3XL

4XL

5XL

G80012

ASHGREY

2XL

$4.43

78

7

RegularPrice

$2.32

$4.91

$6.34

$6.28

G80012

ASHGREY

3XL

$6.03

0

8

SpecialPrice

$2.03

G80012

ASHGREY

4XL

$5.98

10

9

Total-Inventory

1646

2000

1355

436

427

70

0

G80012

ASHGREY

5XL

$5.99

11

10

BLACK

S

M

L

XL

2XL

3XL

4XL

5XL

G80012

AZALEA

S

$2.32

$2.03

1646

11

RegularPrice

$2.32

$4.91

$6.34

$6.28

G80012

AZALEA

M

$2.32

$2.03

2000

12

SpecialPrice

$2.03

G80012

AZALEA

L

$2.32

$2.03

1355

13

Total-Inventory

35935

56924

67178

28203

10723

5075

144

1006

G80012

AZALEA

XL

$2.32

$2.03

436

14

G80012

AZALEA

2XL

$4.91

427

15

G80012

AZALEA

3XL

$6.34

70

16

G80012

AZALEA

4XL

$6.28

0

17

G80012

AZALEA

5XL

$6.28

0

18

G80012

BLACK

S

$2.32

$2.03

35935

19

G80012

BLACK

M

$2.32

$2.03

56924

20

G80012

BLACK

L

$2.32

$2.03

67178

21

G80012

BLACK

XL

$2.32

$2.03

28203

22

G80012

BLACK

2XL

$4.91

10723

23

G80012

BLACK

3XL

$6.34

5075

24

G80012

BLACK

4XL

$6.28

144

25

G80012

BLACK

5XL

$6.28

1006

Sheet1

devint · Dec 18, 2020

Hi Peter,

Thank you for the response, tip, and the VBA code! Your code works well to get what I am looking for. However, every now and then I get a runtime error, and also get this runtime error when I try to run more than 200 lines (I have about 3000 lines).

According to the debugger, this line is the issue.

VBA Code:

        [COLOR=rgb(184, 49, 47)]b(r, 3 + k) = a(i + k, j)[/COLOR]

What is causing this to only work sometimes, and not others?

Thanks again for your help, it is very much appreciated!

devint · Dec 18, 2020

Actually, I believe I found the problem.

Some colors listed do not include the SpecialPrice row in the data set. Is there some way to alter the VBA code to account for that and leave that part blank in the new dataset?

devint · Dec 18, 2020

Here is an example of the data set. Highlighted in yellow is a color without a special price row.

Also, sorry for the multiple responses - no edit button that I can see

Test.xlsm

A

B

C

D

E

F

G

H

I

1

G800

2

ASHGREY

S

M

L

XL

2XL

3XL

4XL

5XL

3

RegularPrice

2.24

4.43

6.03

5.98

5.99

4

SpecialPrice

1.92

5

Total-Inventory

27322

4868

16127

2190

78

0

10

11

6

AZALEA

S

M

L

XL

2XL

3XL

4XL

5XL

7

RegularPrice

2.32

4.91

6.34

6.28

8

SpecialPrice

2.03

9

Total-Inventory

1646

2000

1355

436

427

70

0

10

BLACK

S

M

L

XL

2XL

3XL

4XL

5XL

11

RegularPrice

2.32

4.91

6.34

6.28

12

SpecialPrice

2.03

13

Total-Inventory

35935

56924

67178

28203

10723

5075

144

1006

14

CAROLINABLUE

S

M

L

XL

2XL

3XL

4XL

5XL

15

RegularPrice

2.32

4.91

6.34

6.28

16

SpecialPrice

2.03

17

Total-Inventory

27833

5947

3580

4363

4179

556

0

18

DAISY

S

M

L

XL

2XL

3XL

4XL

5XL

19

RegularPrice

2.32

4.91

6.34

6.28

20

Total-Inventory

27886

10266

4470

9997

1126

2584

9

0

21

DARKCHOCOLATE

S

M

L

XL

2XL

3XL

4XL

5XL

22

RegularPrice

2.56

4.46

5.99

23

SpecialPrice

2.03

0.75

2.03

24

Total-Inventory

0

51

0

25

DARKHEATHER

S

M

L

XL

2XL

3XL

4XL

5XL

26

RegularPrice

2.32

4.91

6.34

6.28

27

SpecialPrice

2.03

28

Total-Inventory

22425

9243

13362

3286

2633

0

31

29

ELECTRICGREEN

S

M

L

XL

2XL

3XL

4XL

5XL

30

RegularPrice

2.32

4.91

6.34

6.28

31

SpecialPrice

2.03

32

Total-Inventory

20806

21523

19842

12098

3409

796

313

0

Sheet1

alansidman · Dec 18, 2020

With your sample here is an alternative solution using Power Query

Book3

A

B

C

D

E

F

1

Data.G80012

Data.ASHGREY

Attribute

RegularPrice

SpecialPrice

Total-Inventory

2

G80012

ASHGREY

2XL

$4.43

78

3

G80012

ASHGREY

3XL

$6.03

0

4

G80012

ASHGREY

4XL

$5.98

10

5

G80012

ASHGREY

5XL

$5.99

11

6

G80012

ASHGREY

L

$2.24

$1.92

16127

7

G80012

ASHGREY

M

$2.24

$1.92

4868

8

G80012

ASHGREY

S

$2.24

$1.92

27322

9

G80012

ASHGREY

XL

$2.24

$1.92

2190

10

G80012

AZALEA

2XL

$4.91

427

11

G80012

AZALEA

3XL

$6.34

70

12

G80012

AZALEA

4XL

$6.28

0

13

G80012

AZALEA

5XL

$6.28

0

14

G80012

AZALEA

L

$2.32

$2.03

1355

15

G80012

AZALEA

M

$2.32

$2.03

2000

16

G80012

AZALEA

S

$2.32

$2.03

1646

17

G80012

AZALEA

XL

$2.32

$2.03

436

18

G80012

BLACK

2XL

$4.91

10723

19

G80012

BLACK

3XL

$6.34

5075

20

G80012

BLACK

4XL

$6.28

144

21

G80012

BLACK

5XL

$6.28

1006

22

G80012

BLACK

L

$2.32

$2.03

67178

23

G80012

BLACK

M

$2.32

$2.03

56924

24

G80012

BLACK

S

$2.32

$2.03

35935

25

G80012

BLACK

XL

$2.32

$2.03

28203

Table1

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"G80012", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Style", each if Text.StartsWith([Column1],"G") then [Column1] else null),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Style", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
    #"Filled Down" = Table.FillDown(#"Reordered Columns",{"Style"}),
    #"Removed Top Rows" = Table.Skip(#"Filled Down",1),
    #"Added Custom1" = Table.AddColumn(#"Removed Top Rows", "Color", each if Text.StartsWith([Column2], "S") then [Column1] else null),
    #"Filled Down1" = Table.FillDown(#"Added Custom1",{"Color"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Filled Down1",{"Style", "Color", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Reordered Columns1", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"G80012", type text}, {"ASHGREY", type text}, {"ASHGREY_1", type text}, {"S", type text}, {"M", type text}, {"L", type text}, {"XL", type text}, {"2XL", type text}, {"3XL", type text}, {"4XL", type text}, {"5XL", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([S] <> "S")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"ASHGREY_1"}, {{"Data", each _, type table [G80012=nullable text, ASHGREY=nullable text, ASHGREY_1=nullable text, S=nullable text, M=nullable text, L=nullable text, XL=nullable text, 2XL=nullable text, 3XL=nullable text, 4XL=nullable text, 5XL=nullable text]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"G80012", "ASHGREY", "S", "M", "L", "XL", "2XL", "3XL", "4XL", "5XL"}, {"Data.G80012", "Data.ASHGREY", "Data.S", "Data.M", "Data.L", "Data.XL", "Data.2XL", "Data.3XL", "Data.4XL", "Data.5XL"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Data", {"ASHGREY_1", "Data.G80012", "Data.ASHGREY"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[ASHGREY_1]), "ASHGREY_1", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column","Data.","",Replacer.ReplaceText,{"Attribute"})
in
    #"Replaced Value"

alansidman · Dec 18, 2020

With your Highlighted Table, here is the Mcode for Power Query

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"G800", type text}, {"G801", type any}, {"G802", type any}, {"G803", type any}, {"G804", type any}, {"G805", type any}, {"G806", type any}, {"G807", type any}, {"G808", type any}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if[G801]= "S" then[G800] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([G801] <> "S")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Custom", "G800", "G801", "G802", "G803", "G804", "G805", "G806", "G807", "G808"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Color"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Color", "G800"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[G800]), "G800", "Value", List.Sum)
in
    #"Pivoted Column"

OOPs! forgot to bring in sizes. Will rerun and post again.

Book3

A

B

C

D

E

1

Color

Attribute

RegularPrice

SpecialPrice

Total-Inventory

2

ASHGREY

G801

2.24

1.92

27322

3

ASHGREY

G802

2.24

1.92

4868

4

ASHGREY

G803

2.24

1.92

16127

5

ASHGREY

G804

2.24

1.92

2190

6

ASHGREY

G805

4.43

78

7

ASHGREY

G806

6.03

0

8

ASHGREY

G807

5.98

10

9

ASHGREY

G808

5.99

11

10

AZALEA

G801

2.32

2.03

1646

11

AZALEA

G802

2.32

2.03

2000

12

AZALEA

G803

2.32

2.03

1355

13

AZALEA

G804

2.32

2.03

436

14

AZALEA

G805

4.91

427

15

AZALEA

G806

6.34

70

16

AZALEA

G807

6.28

0

17

AZALEA

G808

6.28

0

18

BLACK

G801

2.32

2.03

35935

19

BLACK

G802

2.32

2.03

56924

20

BLACK

G803

2.32

2.03

67178

21

BLACK

G804

2.32

2.03

28203

22

BLACK

G805

4.91

10723

23

BLACK

G806

6.34

5075

24

BLACK

G807

6.28

144

25

BLACK

G808

6.28

1006

26

CAROLINABLUE

G801

2.32

2.03

27833

27

CAROLINABLUE

G802

2.32

2.03

5947

28

CAROLINABLUE

G803

2.32

2.03

3580

29

CAROLINABLUE

G804

2.32

2.03

4363

30

CAROLINABLUE

G805

4.91

4179

31

CAROLINABLUE

G806

6.34

556

32

CAROLINABLUE

G807

6.28

0

33

CAROLINABLUE

G808

6.28

0

34

DAISY

G801

2.32

27886

35

DAISY

G802

2.32

10266

36

DAISY

G803

2.32

4470

37

DAISY

G804

2.32

9997

38

DAISY

G805

4.91

1126

39

DAISY

G806

6.34

2584

40

DAISY

G807

6.28

9

41

DAISY

G808

6.28

0

42

DARKCHOCOLATE

G801

2.56

2.03

0

43

DARKCHOCOLATE

G802

2.56

0.75

0

44

DARKCHOCOLATE

G803

2.56

2.03

51

45

DARKCHOCOLATE

G804

2.56

2.03

0

46

DARKCHOCOLATE

G805

4.46

0

47

DARKCHOCOLATE

G806

5.99

0

48

DARKCHOCOLATE

G807

5.99

0

49

DARKCHOCOLATE

G808

5.99

0

50

DARKHEATHER

G801

2.32

2.03

22425

51

DARKHEATHER

G802

2.32

2.03

9243

52

DARKHEATHER

G803

2.32

2.03

13362

53

DARKHEATHER

G804

2.32

2.03

3286

54

DARKHEATHER

G805

4.91

2633

55

DARKHEATHER

G806

6.34

0

56

DARKHEATHER

G807

6.28

0

57

DARKHEATHER

G808

6.28

31

58

ELECTRICGREEN

G801

2.32

2.03

20806

59

ELECTRICGREEN

G802

2.32

2.03

21523

60

ELECTRICGREEN

G803

2.32

2.03

19842

61

ELECTRICGREEN

G804

2.32

2.03

12098

62

ELECTRICGREEN

G805

4.91

3409

63

ELECTRICGREEN

G806

6.34

796

64

ELECTRICGREEN

G807

6.28

313

65

ELECTRICGREEN

G808

6.28

0

Table1 (2)

Peter_SSs · Dec 18, 2020

devint said:
Also, sorry for the multiple responses - no edit button that I can see

That is an anti-spam measure for new members. Once you have made a few more posts you will have established yourself as a legitimate member and have a 10-minute window after posting to edit.

devint said:
Some colors listed do not include the SpecialPrice row in the data set.

That is certainly why my code errored as your original sample data all had 4 rows per section I assumed that always the case.
Try this version.

VBA Code:

Sub Rearrange_v2()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, r As Long, uba1 As Long, uba2 As Long, Col As Long
  Dim sStyle As String, sColor As String
  
  a = Range("A1").CurrentRegion.Value
  uba1 = UBound(a, 1)
  uba2 = UBound(a, 2)
  ReDim b(1 To (uba2 - 1) * (uba1 - 1), 1 To 6)
  sStyle = a(1, 1)
  i = 2
  k = 0
  Do
    Do
      Select Case a(i, 1)
        Case "RegularPrice": Col = 4
        Case "SpecialPrice": Col = 5
        Case "Total-Inventory": Col = 6
        Case Else
          Col = 2
          r = k
          sColor = a(i, 1)
          For j = 2 To uba2
            b(r + j - 1, 1) = sStyle
            b(r + j - 1, 2) = sColor
            b(r + j - 1, 3) = a(i, j)
          Next j
      End Select
      If Col > 2 Then
        r = k
        For j = 2 To uba2
          b(r + j - 1, Col) = a(i, j)
        Next j
      End If
      i = i + 1
    Loop Until Col = 6
    k = k + uba2 - 1
  Loop Until i > uba1
  Cells(1, uba2 + 2).Resize(, 6).Value = Split("Style|Color|Size|RegularPrice|SpecialPrice|Total-Inventory", "|")
  Cells(2, uba2 + 2).Resize(k, 6).Value = b
End Sub

devint · Dec 18, 2020

Peter_SSs said:
That is certainly why my code errored as your original sample data all had 4 rows per section I assumed that always the case.
Try this version.

Yeah, my apologies, I just took the top part of my data set for the example and completely spaced on that aspect. The updated version works perfectly.

While working on this today - I was able to manage to pull the data with the style number in its own column, instead of on top by itself (The style number changes much farther down the dataset). Would that require much editing to add to this script?

Before example:

AB Top 30 - 12-3-2020 UF - Copy.xlsx

A

B

C

D

E

F

G

H

I

J

1

G800

ASHGREY

S

M

L

XL

2XL

3XL

4XL

5XL

2

G800

RegularPrice

$2.24

$4.43

$6.03

$5.98

$5.99

3

G800

SpecialPrice

$1.92

4

G800

Total-Inventory

27322

4868

16127

2190

78

0

10

11

5

G800

AZALEA

S

M

L

XL

2XL

3XL

4XL

5XL

6

G800

RegularPrice

$2.32

$4.91

$6.34

$6.28

7

G800

SpecialPrice

$2.03

8

G800

Total-Inventory

1646

2000

1355

436

427

70

0

9

G800

BLACK

S

M

L

XL

2XL

3XL

4XL

5XL

10

G800

RegularPrice

$2.32

$4.91

$6.34

$6.28

11

G800

SpecialPrice

$2.03

12

G800

Total-Inventory

35935

56924

67178

28203

10723

5075

144

1006

13

G500

CAROLINABLUE

S

M

L

XL

2XL

3XL

4XL

5XL

14

G500

RegularPrice

$2.32

$4.91

$6.34

$6.28

15

G500

SpecialPrice

$2.03

16

G500

Total-Inventory

27833

5947

3580

4363

4179

556

0

17

G500

DAISY

S

M

L

XL

2XL

3XL

4XL

5XL

18

G500

RegularPrice

$2.32

$4.91

$6.34

$6.28

19

G500

SpecialPrice

$2.03

20

G500

Total-Inventory

27886

10266

4470

9997

1126

2584

9

0

21

G500

DARKCHOCOLATE

S

M

L

XL

2XL

3XL

4XL

5XL

22

G500

RegularPrice

$2.56

$4.46

$5.99

23

G500

SpecialPrice

$2.03

$0.75

$2.03

24

G500

Total-Inventory

0

51

0

Sheet1

After example (same format):

AB Top 30 - 12-3-2020 UF - Copy.xlsx

L

M

N

O

P

Q

1

Style

Color

Size

RegularPrice

SpecialPrice

Total-Inventory

2

G800

ASHGREY

S

$2.24

$1.92

27322

3

G800

ASHGREY

M

$2.24

$1.92

4868

4

G800

ASHGREY

L

$2.24

$1.92

16127

5

G800

ASHGREY

XL

$2.24

$1.92

2190

6

G800

ASHGREY

2XL

$4.43

78

7

G800

ASHGREY

3XL

$6.03

0

8

G800

ASHGREY

4XL

$5.98

10

9

G800

ASHGREY

5XL

$5.99

11

10

G800

ASHGREY

11

G800

AZALEA

S

$2.32

$2.03

1646

12

G800

AZALEA

M

$2.32

$2.03

2000

13

G800

AZALEA

L

$2.32

$2.03

1355

14

G800

AZALEA

XL

$2.32

$2.03

436

15

G800

AZALEA

2XL

$4.91

427

16

G800

AZALEA

3XL

$6.34

70

17

G800

AZALEA

4XL

$6.28

0

18

G800

AZALEA

5XL

$6.28

0

19

G800

AZALEA

20

G800

BLACK

S

$2.32

$2.03

35935

21

G800

BLACK

M

$2.32

$2.03

56924

22

G800

BLACK

L

$2.32

$2.03

67178

23

G800

BLACK

XL

$2.32

$2.03

28203

24

G800

BLACK

2XL

$4.91

10723

25

G800

BLACK

3XL

$6.34

5075

26

G800

BLACK

4XL

$6.28

144

27

G800

BLACK

5XL

$6.28

1006

28

G800

BLACK

29

G500

CAROLINABLUE

S

$2.32

$2.03

27833

30

G500

CAROLINABLUE

M

$2.32

$2.03

5947

31

G500

CAROLINABLUE

L

$2.32

$2.03

3580

32

G500

CAROLINABLUE

XL

$2.32

$2.03

4363

33

G500

CAROLINABLUE

2XL

$4.91

4179

34

G500

CAROLINABLUE

3XL

$6.34

556

35

G500

CAROLINABLUE

4XL

$6.28

0

36

G500

CAROLINABLUE

5XL

$6.28

0

37

G500

CAROLINABLUE

Sheet1

Note: This doesn't bother me at all, as it's pretty easy to eliminate, but there is a blank line after the sizes end (I assume it goes to a certain column when transposing, example on line 10), is it easy to not include those? Again, not a big deal, it's only a few clicks to remove those.

Thank you very much for your assistance in this. You are a magician!

Devin

Peter_SSs · Dec 18, 2020

devint said:
Would that require much editing to add to this script?

Try

VBA Code:

Sub Rearrange_v3()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, r As Long, uba1 As Long, uba2 As Long, Col As Long
  Dim sStyle As String, sColor As String
  
  a = Range("A1").CurrentRegion.Value
  uba1 = UBound(a, 1)
  uba2 = UBound(a, 2)
  ReDim b(1 To (uba2 - 1) * (uba1 - 1), 1 To 6)
  i = 1
  k = 0
  Do
    Do
      Select Case a(i, 2)
        Case "RegularPrice": Col = 4
        Case "SpecialPrice": Col = 5
        Case "Total-Inventory": Col = 6
        Case Else
          Col = 2
          r = k
          sStyle = a(i, 1)
          sColor = a(i, 2)
          For j = 3 To uba2
            b(r + j - 2, 1) = sStyle
            b(r + j - 2, 2) = sColor
            b(r + j - 2, 3) = a(i, j)
          Next j
      End Select
      If Col > 2 Then
        r = k
        For j = 3 To uba2
          b(r + j - 2, Col) = a(i, j)
        Next j
      End If
      i = i + 1
    Loop Until Col = 6
    k = k + uba2 - 2
  Loop Until i > uba1
  Cells(1, uba2 + 2).Resize(, 6).Value = Split("Style|Color|Size|RegularPrice|SpecialPrice|Total-Inventory", "|")
  Cells(2, uba2 + 2).Resize(k, 6).Value = b
End Sub

Transpose Large Data Help

devint

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

devint

New Member

devint

New Member

devint

New Member

alansidman

Well-known Member

alansidman

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

devint

New Member

Peter_SSs

MrExcel MVP, Moderator

Forum statistics

Share this page

Transpose Large Data Help

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

New Member

New Member

Well-known Member

Well-known Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

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