Re-Configure, duplicate and restructure data set into a new format. Seems complex but Im sure there is a way.

shortjake · Aug 30, 2022

Hi Excel Gurus!

I am in need of some big-time help.

I have received some data that is very close to the format I need but still needs quite a bit of work.
I have a range of vehicles (make/model/year) and SKU's (products) that fit those models. I need the format to be completely changed so that my stock system and website tool can bulk upload the data in the required format.

Data came like this:

Column 1 - MAKE/MODEL/YEAR (seperated by slashes / )
Column 2 - SKU# seperated by a comma.

I need the data to be:

Column 1 - SKU#
Column 2 - Make
Column 3 - Model
Column 4 - Year

However trickey part is that rather than the skus that fit said vehicle be listed horizontally, the skus need to be listed vertically and the vehicle repeated vertically, but also the SKU could fit multiple vehicles, so the SKU also needs to be repeated.

Please see how the data is now vs how it needs to be (I did the "needs to be" manually) Obviously it can be done all manually but will take a very long time given the amount of SKU's and vehicles

So far I have learned to turn the data into a table and used text to column to split everything up. Which is step one. But now I am stuck.

trackpro_makemodelyear.csv

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Column1

Column2

Needs to be like this

2

category

related_skus

Part

Make

Model

Year

3

Aprilia/Caponord 1200/2013

EVO007BL,EVO007CL,JPMJ001-03,PRO007BL,PRO007CL

HB150C

Aprilia

Caponord ETV 1000

2001

4

Aprilia/Caponord 1200/2014

EVO007BL,EVO007CL,JPMJ001-03,PRO007BL,PRO007CL

HB150C

Aprilia

Caponord ETV 1000

2002

5

Aprilia/Caponord 1200/2015

EVO007BL,EVO007CL,JPMJ001-03,PRO007BL,PRO007CL

HB150C

Aprilia

Caponord ETV 1000

2003

6

Aprilia/Caponord 1200/2016

EVO007BL,EVO007CL,JPMJ001-03,PRO007BL,PRO007CL

HB150C

Aprilia

Caponord ETV 1000

2004

7

Aprilia/Caponord ETV 1000/2001

HB150C,JPMJ004-10

HB150C

Aprilia

Caponord ETV 1000

2005

8

Aprilia/Caponord ETV 1000/2002

HB150C,JPMJ004-10

HB150C

Aprilia

Caponord ETV 1000

2006

9

Aprilia/Caponord ETV 1000/2003

HB150C,JPMJ004-10

HB150C

Aprilia

Caponord ETV 1000

2007

10

Aprilia/Caponord ETV 1000/2004

HB150C,JPMJ004-10

JPMJ004-10

Aprilia

Caponord ETV 1000

2001

11

Aprilia/Caponord ETV 1000/2005

HB150C,JPMJ004-10

JPMJ004-10

Aprilia

Caponord ETV 1000

2002

12

Aprilia/Caponord ETV 1000/2006

HB150C,JPMJ004-10

JPMJ004-10

Aprilia

Caponord ETV 1000

2003

13

Aprilia/Caponord ETV 1000/2007

HB150C,JPMJ004-10

JPMJ004-10

Aprilia

Caponord ETV 1000

2004

14

Aprilia/Caponord ETV 1000/2008

JPMJ004-10

Aprilia

Caponord ETV 1000

2005

15

Aprilia/Caponord ETV 1000/2009

JPMJ004-10

Aprilia

Caponord ETV 1000

2006

16

Aprilia/Caponord ETV 1000/2010

JPMJ004-10

Aprilia

Caponord ETV 1000

2007

17

Aprilia/Caponord ETV 1000/2011

JPMJ004-10

Aprilia

Caponord ETV 1000

2008

18

Aprilia/Caponord ETV 1000/2013

JPMJ001-03

JPMJ004-10

Aprilia

Caponord ETV 1000

2009

19

Aprilia/Caponord ETV 1000/2014

JPMJ001-03

JPMJ004-10

Aprilia

Caponord ETV 1000

2010

20

Aprilia/Caponord ETV 1000/2015

JPMJ001-03

JPMJ004-10

Aprilia

Caponord ETV 1000

2011

21

Aprilia/Caponord ETV 1000/2016

JPMJ001-03

JPMJ004-10

Aprilia

Caponord ETV 1000

2013

22

Aprilia/Dorsoduro 1200/2010

DASHAPR001,JPMJ001-01

JPMJ004-10

Aprilia

Caponord ETV 1000

2014

23

Aprilia/Dorsoduro 1200/2011

DASHAPR001,JPMJ001-01

JPMJ004-10

Aprilia

Caponord ETV 1000

2015

24

Aprilia/Dorsoduro 1200/2012

DASHAPR001,JPMJ001-01

JPMJ004-10

Aprilia

Caponord ETV 1000

2016

25

Aprilia/Dorsoduro 1200/2013

DASHAPR001,JPMJ001-01

26

Aprilia/Dorsoduro 750/2008

DASHAPR001,JPMJ001-01,PM108S

27

Aprilia/Dorsoduro 750/2009

DASHAPR001,JPMJ001-01,PM108S

28

Aprilia/Dorsoduro 750/2010

DASHAPR001,JPMJ001-01,PM108S

29

Aprilia/Dorsoduro 750/2011

DASHAPR001,JPMJ001-01,PM108S

30

Aprilia/Dorsoduro 750/2012

DASHAPR001,JPMJ001-01,PM108S

31

Aprilia/Dorsoduro 750/2013

DASHAPR001,JPMJ001-01,PM108S

32

Aprilia/Dorsoduro 750/2014

DASHAPR001,JPMJ001-01,PM108S

Text to Coumns

Column2

33

Aprilia/Dorsoduro 750/2015

DASHAPR001,JPMJ001-01,PM108S

Make

Model

Year

Sku

34

Aprilia/Dorsoduro 750/2016

DASHAPR001,JPMJ001-01,PM108S

Aprilia

Caponord 1200

2013

EVO007BL

EVO007CL

JPMJ001-03

PRO007BL

PRO007CL

35

Aprilia/Dorsoduro 900/2017

DASHAPR006,JPMJ001-01,PM108S

Aprilia

Caponord 1200

2014

EVO007BL

EVO007CL

JPMJ001-03

PRO007BL

PRO007CL

36

Aprilia/Dorsoduro 900/2018

DASHAPR006,JPMJ001-01,PM108S

Aprilia

Caponord 1200

2015

EVO007BL

EVO007CL

JPMJ001-03

PRO007BL

PRO007CL

37

Aprilia/Dorsoduro 900/2019

DASHAPR006,PM108S

Aprilia

Caponord 1200

2016

EVO007BL

EVO007CL

JPMJ001-03

PRO007BL

PRO007CL

38

Aprilia/Dorsoduro 900/2020

DASHAPR006,PM108S

Aprilia

Caponord ETV 1000

2001

HB150C

JPMJ004-10

39

Aprilia/RS660/2021

BA12-6-GBR-SET,CGA09-GBR,DASHAPR012,EC-RS660-2021-1-GBR,EC-RS660-2021-2-GBR,EC-RS660-2021-5-GBR,EC-RS660-2021-SET-GBR,EVO009BL,EVO009CL,GUARDAPR007,GUARDAPR007M,JPPLD660,JPPLDR660,JPPLR660,JPPLSR660,PM176S,PM176S-WP,PM176SF1-85,PRO009BL,PRO009CL

Aprilia

Caponord ETV 1000

2002

HB150C

JPMJ004-10

40

Aprilia/RST Futura/2001

Aprilia

Caponord ETV 1000

2003

HB150C

JPMJ004-10

41

Aprilia/RST Futura/2002

Aprilia

Caponord ETV 1000

2004

HB150C

JPMJ004-10

42

Aprilia/RST Futura/2003

Aprilia

Caponord ETV 1000

2005

HB150C

JPMJ004-10

43

Aprilia/RSV 1000 R Factory/2004

EVO001BL,EVO001CL,PRO001BL,PRO001CL

Aprilia

Caponord ETV 1000

2006

HB150C

JPMJ004-10

44

Aprilia/RSV 1000 R Factory/2005

EVO001BL,EVO001CL,FC102B,FC102G,FC102N,FC102R,FC102S,FC102Y,PM05S,PM05SF1-85,PRO001BL,PRO001CL

Aprilia

Caponord ETV 1000

2007

HB150C

JPMJ004-10

45

Aprilia/RSV 1000 R Factory/2006

EVO001BL,EVO001CL,FC102B,FC102G,FC102N,FC102R,FC102S,FC102Y,PM05S,PM05SF1-85,PRO001BL,PRO001CL

Aprilia

Caponord ETV 1000

2008

JPMJ004-10

46

Aprilia/RSV 1000 R Factory/2007

EVO001BL,EVO001CL,FC102B,FC102G,FC102N,FC102R,FC102S,FC102Y,PM05S,PM05SF1-85,PRO001BL,PRO001CL

Aprilia

Caponord ETV 1000

2009

JPMJ004-10

47

Aprilia/RSV 1000 R Factory/2008

EVO001BL,EVO001CL,FC102B,FC102G,FC102N,FC102R,FC102S,FC102Y,PM05S,PM05SF1-85,PRO001BL,PRO001CL

Aprilia

Caponord ETV 1000

2010

JPMJ004-10

48

Aprilia/RSV 1000 R Factory/2009

EVO001BL,EVO001CL,FC102B,FC102G,FC102N,FC102R,FC102S,FC102Y,PM05S,PM05SF1-85,PRO001BL,PRO001CL

Aprilia

Caponord ETV 1000

2011

JPMJ004-10

49

Aprilia/RSV 1000 R Factory/2010

EVO001BL,EVO001CL,PM05S,PM05SF1-85,PRO001BL,PRO001CL

Aprilia

Caponord ETV 1000

2013

JPMJ001-03

50

Aprilia/RSV Mille/1998

CJP112H-01,JPMJ011-01

Aprilia

Caponord ETV 1000

2014

JPMJ001-03

51

Aprilia/RSV Mille/1999

CJP112H-01,JPMJ011-01

Aprilia

Caponord ETV 1000

2015

JPMJ001-03

52

Aprilia/RSV Mille/2000

CJP112H-01,JPMJ011-01,PM05S,PM05SF1-85

Aprilia

Caponord ETV 1000

2016

JPMJ001-03

53

Aprilia/RSV Mille/2001

CJP112H-01,JPMJ011-01

Aprilia

Dorsoduro 1200

2010

DASHAPR001

JPMJ001-01

54

Aprilia/RSV Mille/2002

CJP112H-01,JPMJ011-01

Aprilia

Dorsoduro 1200

2011

DASHAPR001

JPMJ001-01

55

Aprilia/RSV Mille/2003

CJP112H-01,JPMJ011-01

Aprilia

Dorsoduro 1200

2012

DASHAPR001

JPMJ001-01

56

Aprilia/RSV Tuono/2002

CJP112H-01,JPMJ011-01

Aprilia

Dorsoduro 1200

2013

DASHAPR001

JPMJ001-01

57

Aprilia/RSV Tuono/2003

CJP112H-01,JPMJ011-01

Aprilia

Dorsoduro 750

2008

DASHAPR001

JPMJ001-01

PM108S

58

Aprilia/RSV Tuono/2004

CJP112H-01,JPMJ011-01

Aprilia

Dorsoduro 750

2009

DASHAPR001

JPMJ001-01

PM108S

59

Aprilia/RSV Tuono/2005

CJP112H-01,JPMJ011-01

Aprilia

Dorsoduro 750

2010

DASHAPR001

JPMJ001-01

PM108S

60

Aprilia/RSV4 1100 Factory/2019

BA12-6-RSV4-GBR-SET,CJP014R-01,CP-RSV4-2010-CS-GBR,DASHAPR006,DKS032N,EC-RSV4-2010-1-GBR,EC-RSV4-2010-2-GBR,EC-RSV4-2010-SET-GBR,EVO002BL,EVO002CL,FC102B,FC102G,FC102N,FC102R,FC102S,FC102Y,FS-RSV4-2010-R,JPACC009,JPACC009RV,JPBRDX,JPKS009,JPMJ001-20,JPPLD009,JPPLR009RF,JPPLRB009RF,JPPLSR009RF,PM147S,PM147SF1-85,PRO002BL,PRO002CL

Aprilia

Dorsoduro 750

2011

DASHAPR001

JPMJ001-01

PM108S

61

Aprilia/RSV4 1100 Factory/2020

BA12-6-RSV4-GBR-SET,CJP014R-01,CP-RSV4-2010-CS-GBR,DASHAPR006,DKS032N,EC-RSV4-2010-1-GBR,EC-RSV4-2010-2-GBR,EC-RSV4-2010-SET-GBR,EVO002BL,EVO002CL,FC102B,FC102G,FC102N,FC102R,FC102S,FC102Y,FS-RSV4-2010-R,JPACC009,JPACC009RV,JPBRDX,JPKS009,JPMJ001-20,JPPLD009,JPPLR009RF,JPPLRB009RF,JPPLSR009RF,PM147S,PM147SF1-85,PRO002BL,PRO002CL

Aprilia

Dorsoduro 750

2012

DASHAPR001

JPMJ001-01

PM108S

62

Aprilia/RSV4 1100 Factory/2021

BA12-6-RSV4-GBR-SET,CPM-3,DASHAPR013,DKS032N,EC-RSV4-2010-2-GBR,EC-RSV4-2021-1-GBR,EC-RSV4-2021-SET-GBR,EVO010BL,EVO010CL,FS-RSV4-2010-R,GUARDAPR005,GUARDAPR005M,PM147S,PM147SF1-85,PRO010BL,PRO010CL

Aprilia

Dorsoduro 750

2013

DASHAPR001

JPMJ001-01

PM108S

63

Aprilia/RSV4 Factory APRC/2009

BA12-6-RSV4-GBR-SET,CJP014R-01,CP-RSV4-2010-CS-GBR,CPM-3,DASHAPR001,EC-RSV4-2010-1-GBR,EC-RSV4-2010-2-GBR,EC-RSV4-2010-SET-GBR,EVO002BL,EVO002CL,FC102B,FC102G,FC102N,FC102R,FC102S,FC102Y,FS-RSV4-2010-R,FT002N,FT002R,JPBRDX,JPMJ001-20,JPPL009,JPPLD009,JPPLDB009,JPPLR009,JPPLRB009,JPPLS009,JPPLSR009,PM94S,PRO002BL,PRO002CL

Aprilia

Dorsoduro 750

2014

DASHAPR001

JPMJ001-01

PM108S

64

Aprilia/RSV4 Factory APRC/2010

BA12-6-RSV4-GBR-SET,CJP014R-01,CP-RSV4-2010-CS-GBR,CPM-3,DASHAPR001,EC-RSV4-2010-1-GBR,EC-RSV4-2010-2-GBR,EC-RSV4-2010-SET-GBR,EVO002BL,EVO002CL,FC102B,FC102G,FC102N,FC102R,FC102S,FC102Y,FS-RSV4-2010-R,FT002N,FT002R,JPBRDX,JPKS009,JPMJ001-20,JPPL009,JPPLD009,JPPLDB009,JPPLR009,JPPLRB009,JPPLS009,JPPLSR009,PM94S,PRO002BL,PRO002CL

Aprilia

Dorsoduro 750

2015

DASHAPR001

JPMJ001-01

PM108S

65

Aprilia/RSV4 Factory APRC/2011

BA12-6-RSV4-GBR-SET,CJP014R-01,CP-RSV4-2010-CS-GBR,CPM-3,DASHAPR001,EC-RSV4-2010-1-GBR,EC-RSV4-2010-2-GBR,EC-RSV4-2010-SET-GBR,EVO002BL,EVO002CL,FC102B,FC102G,FC102N,FC102R,FC102S,FC102Y,FS-RSV4-2010-R,FT002N,FT002R,JPBRDX,JPKS009,JPMJ001-20,JPPL009,JPPLD009,JPPLDB009,JPPLR009TC,JPPLRB009TC,JPPLS009,JPPLSR009TC,PM94S,PRO002BL,PRO002CL

Aprilia

Dorsoduro 750

2016

DASHAPR001

JPMJ001-01

PM108S

66

Aprilia/RSV4 Factory APRC/2012

BA12-6-RSV4-GBR-SET,CJP014R-01,CP-RSV4-2010-CS-GBR,CPM-3,DASHAPR001,EC-RSV4-2010-1-GBR,EC-RSV4-2010-2-GBR,EC-RSV4-2010-SET-GBR,EVO002BL,EVO002CL,FC102B,FC102G,FC102N,FC102R,FC102S,FC102Y,FS-RSV4-2010-R,FT002N,FT002R,JPBRDX,JPKS009,JPMJ001-20,JPPL009,JPPLD009,JPPLDB009,JPPLR009TC,JPPLRB009TC,JPPLS009,JPPLSR009TC,PM94S,PRO002BL,PRO002CL

Aprilia

Dorsoduro 900

2017

DASHAPR006

JPMJ001-01

PM108S

67

Aprilia/RSV4 Factory APRC/2013

BA12-6-RSV4-GBR-SET,CJP014R-01,CP-RSV4-2010-CS-GBR,CPM-3,DASHAPR001,EC-RSV4-2010-1-GBR,EC-RSV4-2010-2-GBR,EC-RSV4-2010-SET-GBR,EVO002BL,EVO002CL,FC102B,FC102G,FC102N,FC102R,FC102S,FC102Y,FS-RSV4-2010-R,FT002N,FT002R,JPBRDX,JPKS009,JPMJ001-20,JPPL009,JPPLD009,JPPLDB009,JPPLR009TC,JPPLRB009TC,JPPLS009,JPPLSR009TC,PM94S,PRO002BL,PRO002CL

Aprilia

Dorsoduro 900

2018

DASHAPR006

JPMJ001-01

PM108S

trackpro_makemodelyear

kevin9999 · Sep 1, 2022

shortjake said:
D

Hi mate, Do you mean run this macro after you have done text to columns? Will is take into consideration the output could be well over 100000 rows?

Cheers

No, simply run the code. The code includes the text to columns process.

kevin9999 · Sep 1, 2022

I had problems when I tried to rerun this code today. I've made some amendments and the code below seems to work now.

VBA Code:

Option Explicit
Sub shortjake_2()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    
    Dim rng As Range, rng1 As Range, rng2 As Range
    Set rng1 = ws1.Range("A3", ws1.Cells(Rows.Count, "A").End(xlUp))
    Set rng2 = ws1.Range("B3", ws1.Cells(Rows.Count, "B").End(xlUp))
    
    Dim lRow As Long, lCol1 As Long, lCol2 As Long, lCol3 As Long
    lCol1 = ws1.Cells.Find("*", , xlFormulas, , 2, 2).Column + 2
    lCol2 = lCol1 + 3
    
    Set rng = ws1.Range("A1").CurrentRegion
    rng.Value = Application.Trim(rng)
    
    Application.DisplayAlerts = False
    rng1.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
    rng2.TextToColumns ws1.Cells(1, lCol2), 1, 1, , , , , , , ","
    Application.DisplayAlerts = True
    
    lRow = ws1.Cells(Rows.Count, lCol1).End(xlUp).Row
    lCol3 = ws1.Cells.Find("*", , xlFormulas, , 2, 2).Column
    
    Dim model, sku, arrOut
    model = ws1.Range(ws1.Cells(1, lCol1), ws1.Cells(lRow, lCol1 + 2))
    sku = ws1.Range(ws1.Cells(1, lCol2), ws1.Cells(lRow, lCol3))
    ReDim arrOut(1 To lRow, 1 To 4)
    
    Dim i As Long, j As Long
    
    For i = 1 To UBound(sku, 1)
        For j = 1 To UBound(sku, 2)
            If IsEmpty(sku(i, 1)) Then Exit For
            If Not IsEmpty(sku(i, j)) Then
                arrOut(i, 1) = sku(i, j)
                arrOut(i, 2) = model(i, 1)
                arrOut(i, 3) = model(i, 2)
                arrOut(i, 4) = model(i, 3)
            End If
        Next j
        ws2.Cells(2, 1).Resize(lRow, 4).Value = arrOut
    Next i
    
    ws2.Range("a1").Resize(, 4) = Array("Part", "Make", "Model", "Year")
    ws1.Range(ws1.Cells(1, lCol1), ws1.Cells(1, lCol3)).EntireColumn.Delete
    
    With ws2
        .Range("A1").Resize(, 4) = Array("Part", "Make", "Model", "Year")
        .Range("A:D").Columns.AutoFit
        .Range("A1:D1").Font.Bold = True
    End With
    
    With ws2.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A1"), Order:=xlAscending
        .SortFields.Add Key:=Range("B1"), Order:=xlAscending
        .SortFields.Add Key:=Range("C1"), Order:=xlAscending
        .SortFields.Add Key:=Range("D1"), Order:=xlAscending
        .SetRange Range("A2:D" & lRow + 1)
        .Apply
    End With
End Sub

shortjake · Sep 4, 2022

DO you think it will work on this? Similar situation?

20220820 ALL MODELS W APPLIC EXP XLSX.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

BB

BC

BD

BE

BF

BG

BH

BI

BJ

BK

BL

BM

BN

BO

BP

BQ

BR

BS

BT

BU

BV

BW

BX

BY

BZ

CA

CB

CC

CD

CE

CF

CG

CH

CI

CJ

CK

CL

CM

CN

CO

CP

CQ

CR

CS

CT

CU

CV

CW

CX

CY

CZ

DA

DB

DC

DD

DE

DF

DG

DH

DI

DJ

DK

DL

DM

DN

DO

DP

DQ

DR

DS

DT

DU

DV

DW

DX

DY

DZ

EA

EB

EC

ED

EE

EF

EG

EH

EI

EJ

EK

EL

EM

EN

EO

EP

EQ

ER

ES

ET

EU

EV

EW

EX

EY

EZ

FA

FB

FC

FD

FE

FF

FG

FH

FI

FJ

FK

FL

FM

FN

FO

FP

FQ

FR

FS

FT

FU

FV

FW

FX

FY

FZ

GA

GB

GC

GD

GE

GF

GG

GH

GI

GJ

GK

GL

GM

GN

GO

GP

GQ

GR

GS

GT

GU

GV

GW

GX

GY

GZ

HA

HB

HC

HD

HE

HF

HG

1

ModelID

Make

Model Name

Model Code

Model Year

Chain - OE Size

Front Sprocket

Rear Sprocket - Alloy

Rear Sprocket - Steel

Rear Sprocket - Steel Hybrid

RK Chain - Optional 420

RK Chain - Optional 428

RK Chain - Optional 520

RK Chain - Optional 525

RK Chain - Optional 530

Sprocket Front - Optional 420

Sprocket Front - Optional 428

Sprocket Front - Optional 520

Sprocket Front - Optional 525

Sprocket Front - Optional 530

Sprocket Front - VORTEX 420

Sprocket Front - VORTEX 428

Sprocket Front - VORTEX 520

Sprocket Front - VORTEX 525

Sprocket Front - VORTEX 530

Sprocket Rear - Optional 428

Sprocket Rear - Optional 520

Sprocket Rear - Optional 525

Sprocket Rear - Optional 530

Sprocket Rear - Spr Ducati Adaptor

Sprocket Rear - VORTEX 420

Sprocket Rear - VORTEX 428

Sprocket Rear - VORTEX 520

Sprocket Rear - VORTEX 525

Sprocket Rear - VORTEX 530

Sprocket Rear RK - Alloy

Sprockets & Chain Kits

Sprockets & Chain Kits - Optional

FERODO Brake Front

FERODO Brake Front - Optional

FERODO Brake Rear

FERODO Brake Rear - Optional

FERODO Front Disc

FERODO Rear Disc

FERODO Sidecar or Parking Brake

Maxum-Tech Front Brake Pads

Maxum-Tech Rear Brake Pads

SMX Disc Bolt Kits - Front

SMX Disc Bolt Kits - Rear

States MX Front Disc - Oversize

States MX Front Disc - Race Wave

States MX Rear Disc - Race Wave

Cam Chain

Clutch Fibre Plates

Clutch Fibre Plates HP

Clutch Plate Kits

Clutch Plate Kits HP

Clutch Springs

Air Filter - BMC

Air Filter - BMC Race

Air Filter - CHAMPION

Air Filter Kit

Air Filter- HIFLO

Maintenance - Filter Care

Oil Change Kit

Oil Filter (secondary) - CHAMPION

Oil Filter (secondary) - HIFLO

Oil Filter (Transmission) - CHAMPION

Oil Filter (Transmission) - HIFLO

Oil Filter - CHAMPION

Oil Filter - HIFLO

Oil Filter - HIFLO Optional Long Race Filter

DYNAVOLT - Battery

MOTOCELL GOLD - Lithium Battery

ROADSTAR - Battery

BEARING WORX - Hub Repair Kit - Front

BEARING WORX - Hub Repair Kit - Rear

BEARING WORX - Linkage Kit

BEARING WORX - Shock Mount Lower Kit

BEARING WORX - Shock Mount Upper Kit

BEARING WORX - Steering Bearing Kit

BEARING WORX - Swingarm Kit

BEARING WORX - Wheel Kit - Front

BEARING WORX - Wheel Kit - Rear

LINK - Steering Kit

LINK - Wheel Front

LINK - Wheel L/H Rear

LINK - Wheel R/H Rear

LINK - Wheel Sprocket Carrier

Fork Seal Set

Seal - ARIETE

Seal - LINK

Seal Dust

Telelever Boot

Tube Bush - Inner

Tube Bush - Outer

PIRELLI Front Tyres

PIRELLI Rear Tyres

VEE RUBBER Front Tyres

VEE RUBBER Rear Tyres

Rim STATES MX - Front

Rim STATES MX - Rear

Spoke Set - Front

Spoke Set - Rear

STATES MX - Front Wheel

STATES MX - Rear Wheel

STATES MX - Rear Wheel - Optional Size

STATES MX - Wheel Hub Front

STATES MX - Wheel Hub Rear

STATES MX - Wheel Sets

POLISPORT Air Box

POLISPORT Airbox Cover

POLISPORT Alternator Cover

POLISPORT Bottom Fork Protector

POLISPORT Chain Guide and Slider Kit

POLISPORT Chain Guides

POLISPORT Chain Sliders

POLISPORT Chain Sliding Piece

POLISPORT Clutch and Alt Cover Kit

POLISPORT Clutch Cover Protector

POLISPORT Disc and Bottom Fork Protectors

POLISPORT Disc Protector - Rear

POLISPORT Fender - Front

POLISPORT Fender - Rear

POLISPORT Filter Airbox

POLISPORT Fork Guards

POLISPORT Frame Guards

POLISPORT Front Number Plate

POLISPORT Headlight Surround

POLISPORT Ignition Cover Protector

POLISPORT Perf. Chain Guide Wear Pad

POLISPORT Performance Chain Guide

POLISPORT Rad Scoops - Lower

POLISPORT Rad Scoops - Upper

POLISPORT Radiator Louvre Mesh

POLISPORT Radiator Louvres

POLISPORT Radiator Scoops

POLISPORT Shock Flap

POLISPORT Side Cover - Lower

POLISPORT Side Cover - Upper

POLISPORT Side Covers

POLISPORT Skid Plate/Gaurd

POLISPORT Sprocket Cover

POLISPORT Swingarm Protectors

POLISPORT Tank Covers

POLISPORT Water Pump Guard

KEITI Body Parts Fastener Set

LA CORSA Lever Sets

SMX Axle Blocks

SMX Bar Ends

SMX Billet Kits

SMX Brake Lever - Flex

SMX Brake Lever - Fold & Flex

SMX Brake Pedals

SMX Case Savers

SMX Clutch Lever & Perch Flex

SMX Clutch Lever & Perch Fold & Flex

SMX Clutch Lever & Perch Quick Adjust

SMX Clutch Lever F & F States & OEM

SMX Clutch Levers Flex StatesMX and OEM

SMX Footpegs

SMX Front Brake Line Clamp

SMX Gear Lever Alloy

SMX Hour Meters

SMX Kickstart Lever Alloy

SMX Launch Controls

SMX Pro Pack Bolt Kits

SMX Rear Brake Clevis

SMX Rotator Clamp

SMX Tank Caps

SMX Track Pack Bolt KIts

VORTEX Clip-Ons

VORTEX Fuel Cap Base

VORTEX Fuel Cap Clickers

VORTEX Fuel Caps

VORTEX Lowering Links

VORTEX Rearsets

ARROW Accessories

ARROW Collectors

ARROW DB Killer

ARROW Full System

ARROW Mid-Pipe

ARROW Silencer

ARROW Silencer + Mid-Pipe

POLISPORT - Complete Kits

POLISPORT - Restyle Kits

2 Stroke Oil

Chain Care

Engine Oil

Equipment Care

Final Drive Oil

Fork Oil

Front Diff Oil

Mechanical Care

Oil Change Kits

Paint Plastic and Metal Care

Primary Drive Oil

Transmission Oil

POLISPORT - Graphic Guard

POLISPORT - Lever Set (Brake & Clutch)

POLISPORT - Pipe Guard

POLISPORT - Seat

POLISPORT - Seat Cover - Spare

POLISPORT Restyle - Airbox Cover

POLISPORT Restyle - Fender Front

POLISPORT Restyle - Fender Rear

POLISPORT Restyle - Number Plate

POLISPORT Restyle - Radiator Scoops

POLISPORT Restyle - Side Covers

ARISUN ATV Front Tyres

ARISUN ATV Rear Tyres

ARACHNID Front Tyre

ARACHNID Rear Tyre

Tube Front

Tube Rear

2

80370

ADLY

Nifty 50

HH

2017

61-276-97 61-276-98 61-292-54

61-276-97 61-277-09 61-292-54

16-705-00 16-709-00 16-710-00 16-729-00 16-731-00 26-705-01 26-705-02 26-705-05 26-705-07 26-710-07 26-730-00

16-717-00 16-719-00 16-720-00 16-721-00 16-722-00 26-718-01 26-719-01 26-720-01 26-721-03 26-722-01 26-732-00

16-701-00 16-703-00 16-707-00 16-711-00 26-701-07 26-703-02 26-703-07 26-707-02 26-711-07

16-702-00 16-702-00T 16-712-00 16-712-00T 16-724-00 16-726-00 16-727-00 16-734-00 26-702-02 26-702-07

3

83852

ADLY

Interceptor 150S

ATV

2016

16-705-00 16-709-00 16-710-00 16-729-00 16-731-00 26-705-01 26-705-02 26-705-05 26-705-07 26-710-07 26-730-00

16-717-00 16-719-00 16-720-00 16-721-00 16-722-00 26-718-01 26-719-01 26-720-01 26-721-03 26-722-01 26-732-00

16-701-00 16-703-00 16-707-00 16-711-00 26-701-07 26-703-02 26-703-07 26-707-02 26-711-07

16-702-00 16-702-00T 16-712-00 16-712-00T 16-724-00 16-726-00 16-727-00 16-734-00 26-702-02 26-702-07

76-105-01 76-215-02

4

83851

ADLY

500S

ATV

2012

16-705-00 16-709-00 16-710-00 16-729-00 16-731-00 26-705-01 26-705-02 26-705-05 26-705-07 26-710-07 26-730-00

16-717-00 16-719-00 16-720-00 16-721-00 16-722-00 26-718-01 26-719-01 26-720-01 26-721-03 26-722-01 26-732-00

16-701-00 16-703-00 16-707-00 16-711-00 26-701-07 26-703-02 26-703-07 26-707-02 26-711-07

16-702-00 16-702-00T 16-712-00 16-712-00T 16-724-00 16-726-00 16-727-00 16-734-00 26-702-02 26-702-07

76-105-04

76-106-01 76-216-05

5

83849

ADLY

600U

ATV

2011

16-705-00 16-709-00 16-710-00 16-729-00 16-731-00 26-705-01 26-705-02 26-705-05 26-705-07 26-710-07 26-730-00

16-717-00 16-719-00 16-720-00 16-721-00 16-722-00 26-718-01 26-719-01 26-720-01 26-721-03 26-722-01 26-732-00

16-701-00 16-703-00 16-707-00 16-711-00 26-701-07 26-703-02 26-703-07 26-707-02 26-711-07

16-702-00 16-702-00T 16-712-00 16-712-00T 16-724-00 16-726-00 16-727-00 16-734-00 26-702-02 26-702-07

76-111-01 76-168-01 76-212-11 76-233-01 76-235-01 76-236-01 76-26A-03

76-111-02 76-168-02 76-212-13 76-233-02 76-235-02 76-236-02 76-26A-04

6

83850

ADLY

600U

ATV

2012

16-705-00 16-709-00 16-710-00 16-729-00 16-731-00 26-705-01 26-705-02 26-705-05 26-705-07 26-710-07 26-730-00

16-717-00 16-719-00 16-720-00 16-721-00 16-722-00 26-718-01 26-719-01 26-720-01 26-721-03 26-722-01 26-732-00

16-701-00 16-703-00 16-707-00 16-711-00 26-701-07 26-703-02 26-703-07 26-707-02 26-711-07

16-702-00 16-702-00T 16-712-00 16-712-00T 16-724-00 16-726-00 16-727-00 16-734-00 26-702-02 26-702-07

76-111-01 76-168-01 76-212-11 76-233-01 76-235-01 76-236-01 76-26A-03

76-111-02 76-168-02 76-212-13 76-233-02 76-235-02 76-236-02 76-26A-04

Sheet1

shortjake · Sep 4, 2022

Hi

kevin9999 said:

I had problems when I tried to rerun this code today. I've made some amendments and the code below seems to work now.

VBA Code:

Option Explicit
Sub shortjake_2()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
   
    Dim rng As Range, rng1 As Range, rng2 As Range
    Set rng1 = ws1.Range("A3", ws1.Cells(Rows.Count, "A").End(xlUp))
    Set rng2 = ws1.Range("B3", ws1.Cells(Rows.Count, "B").End(xlUp))
   
    Dim lRow As Long, lCol1 As Long, lCol2 As Long, lCol3 As Long
    lCol1 = ws1.Cells.Find("*", , xlFormulas, , 2, 2).Column + 2
    lCol2 = lCol1 + 3
   
    Set rng = ws1.Range("A1").CurrentRegion
    rng.Value = Application.Trim(rng)
   
    Application.DisplayAlerts = False
    rng1.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
        TrailingMinusNumbers:=True
    rng2.TextToColumns ws1.Cells(1, lCol2), 1, 1, , , , , , , ","
    Application.DisplayAlerts = True
   
    lRow = ws1.Cells(Rows.Count, lCol1).End(xlUp).Row
    lCol3 = ws1.Cells.Find("*", , xlFormulas, , 2, 2).Column
   
    Dim model, sku, arrOut
    model = ws1.Range(ws1.Cells(1, lCol1), ws1.Cells(lRow, lCol1 + 2))
    sku = ws1.Range(ws1.Cells(1, lCol2), ws1.Cells(lRow, lCol3))
    ReDim arrOut(1 To lRow, 1 To 4)
   
    Dim i As Long, j As Long
   
    For i = 1 To UBound(sku, 1)
        For j = 1 To UBound(sku, 2)
            If IsEmpty(sku(i, 1)) Then Exit For
            If Not IsEmpty(sku(i, j)) Then
                arrOut(i, 1) = sku(i, j)
                arrOut(i, 2) = model(i, 1)
                arrOut(i, 3) = model(i, 2)
                arrOut(i, 4) = model(i, 3)
            End If
        Next j
        ws2.Cells(2, 1).Resize(lRow, 4).Value = arrOut
    Next i
   
    ws2.Range("a1").Resize(, 4) = Array("Part", "Make", "Model", "Year")
    ws1.Range(ws1.Cells(1, lCol1), ws1.Cells(1, lCol3)).EntireColumn.Delete
   
    With ws2
        .Range("A1").Resize(, 4) = Array("Part", "Make", "Model", "Year")
        .Range("A:D").Columns.AutoFit
        .Range("A1:D1").Font.Bold = True
    End With
   
    With ws2.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A1"), Order:=xlAscending
        .SortFields.Add Key:=Range("B1"), Order:=xlAscending
        .SortFields.Add Key:=Range("C1"), Order:=xlAscending
        .SortFields.Add Key:=Range("D1"), Order:=xlAscending
        .SetRange Range("A2:D" & lRow + 1)
        .Apply
    End With
End Sub

Hi mate,

It almost worked...

As an example, you can see the YZF-R6 model that there is way way more SKU's that what your code produced. Also my data had over 3000 rows and your code produced only about 3000+ rows where it would have needed to be a substantial amount more considering the amount of skus per model. What do you think??

shortjake · Sep 4, 2022

bebo021999 said:
Check again:
1) Category in column A:
MAKE/MODEL/YEAR (seperated by slashes / )
Is there any category in column A with more than 2 slashes (/)?

2) in debug window, when yellow highlight appears, we can track what variables "i" and "t" current value, by:
Move the mouse pointer to "i" and "t" letter (hover or just click) to see its current value.
What value does "'i" and "t" appear?

Hi Bebo,

Please see below what do you think? What should I do?

bebo021999 · Sep 4, 2022

It seems work for me. Could you attach actual file via free site, i.e, toolbox or google drive?

kevin9999 · Sep 5, 2022

Try this new version

VBA Code:

Option Explicit
Sub shortjake_2()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    
    Dim rng As Range, rng1 As Range, rng2 As Range
    Set rng1 = ws1.Range("A3", ws1.Cells(Rows.Count, "A").End(xlUp))
    Set rng2 = ws1.Range("B3", ws1.Cells(Rows.Count, "B").End(xlUp))
    
    Dim lRow As Long, lCol1 As Long, lCol2 As Long, lCol3 As Long
    lCol1 = ws1.Cells.Find("*", , xlFormulas, , 2, 2).Column + 2
    lCol2 = lCol1 + 3
    
    Set rng = ws1.Range("A1").CurrentRegion
    rng.Value = Application.Trim(rng)
    
    Application.DisplayAlerts = False
    rng1.TextToColumns ws1.Cells(1, lCol1), 1, 1, , , , , , 1, "/"
    rng2.TextToColumns ws1.Cells(1, lCol2), 1, 1, , , , , , , ","
    Application.DisplayAlerts = True
    
    lRow = ws1.Cells(Rows.Count, lCol1).End(xlUp).Row
    lCol3 = ws1.Cells.Find("*", , xlFormulas, , 2, 2).Column
    
    Dim model, sku, arrOut
    model = ws1.Range(ws1.Cells(1, lCol1), ws1.Cells(lRow, lCol1 + 2))
    sku = ws1.Range(ws1.Cells(1, lCol2), ws1.Cells(lRow, lCol3))
    
    Dim x As Long
    x = WorksheetFunction.CountA(ws1.Range(ws1.Cells(1, lCol2), ws1.Cells(lRow, lCol3)))
    
    ReDim arrOut(1 To x, 1 To 4)
    Dim i As Long, j As Long, k As Long
    k = 1
    For i = 1 To UBound(sku, 1)
        For j = 1 To UBound(sku, 2)
            If IsEmpty(sku(i, 1)) Then Exit For
            If Not IsEmpty(sku(i, j)) Then
                arrOut(k, 1) = sku(i, j)
                arrOut(k, 2) = model(i, 1)
                arrOut(k, 3) = model(i, 2)
                arrOut(k, 4) = model(i, 3)
                k = k + 1
            End If
        Next j
    Next i
    
    ws2.Cells(2, 1).Resize(x, 4).Value = arrOut
    ws1.Range(ws1.Cells(1, lCol1), ws1.Cells(1, lCol3)).EntireColumn.Delete
    
    With ws2
        .Range("A1").Resize(, 4) = Array("Part", "Make", "Model", "Year")
        .Range("A:D").Columns.AutoFit
        .Range("A1:D1").Font.Bold = True
    End With
    
    With ws2.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A1"), Order:=xlAscending
        .SortFields.Add Key:=Range("B1"), Order:=xlAscending
        .SortFields.Add Key:=Range("C1"), Order:=xlAscending
        .SortFields.Add Key:=Range("D1"), Order:=xlAscending
        .SetRange Range("A2:D" & lRow + 1)
        .Apply
    End With
End Sub

shortjake · Sep 5, 2022

kevin9999 said:

Try this new version

VBA Code:

Option Explicit
Sub shortjake_2()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
   
    Dim rng As Range, rng1 As Range, rng2 As Range
    Set rng1 = ws1.Range("A3", ws1.Cells(Rows.Count, "A").End(xlUp))
    Set rng2 = ws1.Range("B3", ws1.Cells(Rows.Count, "B").End(xlUp))
   
    Dim lRow As Long, lCol1 As Long, lCol2 As Long, lCol3 As Long
    lCol1 = ws1.Cells.Find("*", , xlFormulas, , 2, 2).Column + 2
    lCol2 = lCol1 + 3
   
    Set rng = ws1.Range("A1").CurrentRegion
    rng.Value = Application.Trim(rng)
   
    Application.DisplayAlerts = False
    rng1.TextToColumns ws1.Cells(1, lCol1), 1, 1, , , , , , 1, "/"
    rng2.TextToColumns ws1.Cells(1, lCol2), 1, 1, , , , , , , ","
    Application.DisplayAlerts = True
   
    lRow = ws1.Cells(Rows.Count, lCol1).End(xlUp).Row
    lCol3 = ws1.Cells.Find("*", , xlFormulas, , 2, 2).Column
   
    Dim model, sku, arrOut
    model = ws1.Range(ws1.Cells(1, lCol1), ws1.Cells(lRow, lCol1 + 2))
    sku = ws1.Range(ws1.Cells(1, lCol2), ws1.Cells(lRow, lCol3))
   
    Dim x As Long
    x = WorksheetFunction.CountA(ws1.Range(ws1.Cells(1, lCol2), ws1.Cells(lRow, lCol3)))
   
    ReDim arrOut(1 To x, 1 To 4)
    Dim i As Long, j As Long, k As Long
    k = 1
    For i = 1 To UBound(sku, 1)
        For j = 1 To UBound(sku, 2)
            If IsEmpty(sku(i, 1)) Then Exit For
            If Not IsEmpty(sku(i, j)) Then
                arrOut(k, 1) = sku(i, j)
                arrOut(k, 2) = model(i, 1)
                arrOut(k, 3) = model(i, 2)
                arrOut(k, 4) = model(i, 3)
                k = k + 1
            End If
        Next j
    Next i
   
    ws2.Cells(2, 1).Resize(x, 4).Value = arrOut
    ws1.Range(ws1.Cells(1, lCol1), ws1.Cells(1, lCol3)).EntireColumn.Delete
   
    With ws2
        .Range("A1").Resize(, 4) = Array("Part", "Make", "Model", "Year")
        .Range("A:D").Columns.AutoFit
        .Range("A1:D1").Font.Bold = True
    End With
   
    With ws2.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A1"), Order:=xlAscending
        .SortFields.Add Key:=Range("B1"), Order:=xlAscending
        .SortFields.Add Key:=Range("C1"), Order:=xlAscending
        .SortFields.Add Key:=Range("D1"), Order:=xlAscending
        .SetRange Range("A2:D" & lRow + 1)
        .Apply
    End With
End Sub

Thank you Kevin!

Cheers

kevin9999 · Sep 5, 2022

shortjake said:
Thank you Kevin!

Cheers

Glad we could help Jake, and thanks for the feedback

Re-Configure, duplicate and restructure data set into a new format. Seems complex but Im sure there is a way.

shortjake

New Member

kevin9999

Well-known Member

Excel Facts

kevin9999

Well-known Member

shortjake

New Member

shortjake

New Member

Attachments

shortjake

New Member

Attachments

bebo021999

Well-known Member

kevin9999

Well-known Member

shortjake

New Member

kevin9999

Well-known Member

Similar threads

Forum statistics

Share this page

Re-Configure, duplicate and restructure data set into a new format. Seems complex but Im sure there is a way.

New Member

Well-known Member

Excel Facts

Well-known Member

New Member

New Member

Attachments

New Member

Attachments

Well-known 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