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.
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 | JPMJ004-10 | Aprilia | Caponord ETV 1000 | 2005 | ||||||||||
15 | Aprilia/Caponord ETV 1000/2009 | JPMJ004-10 | JPMJ004-10 | Aprilia | Caponord ETV 1000 | 2006 | ||||||||||
16 | Aprilia/Caponord ETV 1000/2010 | JPMJ004-10 | JPMJ004-10 | Aprilia | Caponord ETV 1000 | 2007 | ||||||||||
17 | Aprilia/Caponord ETV 1000/2011 | JPMJ004-10 | 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 | Sku | Sku | Sku | Sku | Sku | 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 |