VBA: Turn comma separated list(s) into one master list.

[SteveOranjinSteve]

Hello,

Hope you're well. I'm looking to create a master data sheet that I can work from for the purpose of turning this into a pivot table. There are 3 comma separated lists within the WIDER excel sheet that I'm looking to reduce, each, into a line by line item. This is the format of that data, and then I will show you how I would like it to look. I also have a sheet of the data attached.

Product	SKU Description	Spare Part(SP)/Finished Good (FG)	In SAP?	Name (New - RFI list)	JDE	DIV list
0.617.096	HS 800 Sealing Machine	FG	No	Croatia, Greece, Morocco, Spain, South Africa	Andorra, United Arab Emirates, Afghanistan, Antigua and Barbuda, Albania, Armenia, Netherlands Antilles, Angola, Argentina, Austria, Australia, Aruba, Azerbaijan, Bosnia and Herzegovina, Barbados, Bangladesh, Belgium, Burkina Faso, Bulgaria, Bahrain, Burundi	Croatia, Greece, Morocco
0.617.067	HS 800 Sealing Machine	FG	No		Andorra, United Arab Emirates, Afghanistan, Antigua and Barbuda, Albania, Armenia, Netherlands Antilles, Angola, Argentina, Austria, Australia, Aruba, Azerbaijan, Bosnia and Herzegovina, Barbados, Bangladesh, Belgium, Burkina Faso, Bulgaria, Bahrain, Burundi, Benin

I'd like it to look like this.

Product	SKU Description	Spare Part/Finished Good	In SAP	List	Country
0.617.096	HS 800 Sealing Machine	FG	No	RFI	Croatia
0.617.096	HS 800 Sealing Machine	FG	No	RFI	Greece
0.617.096	HS 800 Sealing Machine	FG	No	RFI	Morocco
0.617.096	HS 800 Sealing Machine	FG	No	RFI	Spain
0.617.096	HS 800 Sealing Machine	FG	No	RFI	South Africa
0.617.096	HS 800 Sealing Machine	FG	No	JDE	Andorra

Is this something that is possible?

Please see Copy of Comparison - Pivot 6.8.2.1.xlsm

Steve

 

[SteveOranjinSteve]

Can I get a bump

 

[alansidman]

In your request you indicate that there are 3 columns of comma separated data. In your file there are 4 columns of comma separated data. Please clarify which columns need to be split apart and unpivoted. What happens if there are duplicate countries listed? Not sure I have an answer for you but I am trying to sort it all out. A smaller sample file with a mocked up solution would have helped here. The more information you provide the quicker you may receive a response.

BTW. You bumped the thread after only 90 minutes. This is a worldwide forum and while you may be awake and looking for a solution, others are working or sleeping or just out having fun. It is customary to wait 24 hours before bumping a thread.

 

[SteveOranjinSteve]

ONLY those first three columns, not the fourth, will be necessary. I already have that fourth column. I should've made that clear. You are right.

If there is a duplicate country in there, that is fine, if it is easy to do, have excel mark it or something in the result so I can go back and remove it.

Steve

 

[SteveOranjinSteve]

So if there is a DUPLICATE country within a single list, then mark it, otherwise it is NOT a duplicate. So a duplicate would be.

Product	SKU Description	Spare Part/Finished Good	In SAP	List	Country
0.617.096	HS 800 Sealing Machine	FG	No	RFI	Croatia
0.617.096	HS 800 Sealing Machine	FG	No	RFI	Greece
0.617.096	HS 800 Sealing Machine	FG	No	RFI	Morocco
0.617.096	HS 800 Sealing Machine	FG	No	RFI	Spain
0.617.096	HS 800 Sealing Machine	FG	No	RFI	South Africa
0.617.096	HS 800 Sealing Machine	FG	No	JDE	Andorra	A
0.617.096	HS 800 Sealing Machine	FG	No	JDE	Andorra	A
0.617.096	HS 800 Sealing Machine	FG	No	JDE	Andorra	B
0.617.096	HS 800 Sealing Machine	FG	No	DIV	Andorra	B

If you look into the right most column there, you'll see the last four are marked "A" and "B". The "A" set is a full on duplicate. "B" is not a full on duplicate, it is therefore not a duplicate.

 

[alansidman]

Check out Gil's page here on converting the comma separated list to rows and unpivoting the columns. He employs Power Query to make this happen

Split Unpivot comma-separated values Power Query

Learn hot to Split Unpivot Comma Separated Values Power Query. Follow tips and tricks to split csv data.

datachant.com

 

[lrobbo314]

Here is a way to do it with Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Product", "SKU Description", "Spare Part(SP)/Finished Good (FG)", "In SAP?"}, "Attribute", "Value"),
    List = Table.AddColumn(Unpivot, "Custom", each Text.Split([Value],", ")),
    Remove = Table.RemoveColumns(List,{"Value"}),
    Expanded = Table.ExpandListColumn(Remove, "Custom"),
    Rename = Table.RenameColumns(Expanded,{{"Attribute", "List"}, {"Custom", "Country"}}),
    Replace = Table.ReplaceValue(Rename,"Name (New - RFI list)","RFI",Replacer.ReplaceText,{"List"})
in
    Replace

Book1
	A	B	C	D	E	F	G
1	Product	SKU Description	Spare Part(SP)/Finished Good (FG)	In SAP?	Name (New - RFI list)	JDE	DIV List
2	0.617.096	HS 800 Sealing Machine	FG	No	Croatia, Greece, Morocco, Spain, South Africa	Andorra, United Arab Emirates, Afghanistan, Antigua and Barbuda, Albania, Armenia, Netherlands Antilles, Angola, Argentina, Austria, Australia, Aruba, Azerbaijan, Bosnia and Herzegovina, Barbados, Bangladesh, Belgium, Burkina Faso, Bulgaria, Bahrain, Burundi	Croatia, Greece, Morocco
3	0.617.067	HS 800 Sealing Machine	FG	No		Andorra, United Arab Emirates, Afghanistan, Antigua and Barbuda, Albania, Armenia, Netherlands Antilles, Angola, Argentina, Austria, Australia, Aruba, Azerbaijan, Bosnia and Herzegovina, Barbados, Bangladesh, Belgium, Burkina Faso, Bulgaria, Bahrain, Burundi, Benin
4
5	Product	SKU Description	Spare Part(SP)/Finished Good (FG)	In SAP?	List	Country
6	0.617.096	HS 800 Sealing Machine	FG	No	RFI	Croatia
7	0.617.096	HS 800 Sealing Machine	FG	No	RFI	Greece
8	0.617.096	HS 800 Sealing Machine	FG	No	RFI	Morocco
9	0.617.096	HS 800 Sealing Machine	FG	No	RFI	Spain
10	0.617.096	HS 800 Sealing Machine	FG	No	RFI	South Africa
11	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Andorra
12	0.617.096	HS 800 Sealing Machine	FG	No	JDE	United Arab Emirates
13	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Afghanistan
14	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Antigua and Barbuda
15	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Albania
16	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Armenia
17	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Netherlands Antilles
18	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Angola
19	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Argentina
20	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Austria
21	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Australia
22	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Aruba
23	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Azerbaijan
24	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Bosnia and Herzegovina
25	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Barbados
26	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Bangladesh
27	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Belgium
28	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Burkina Faso
29	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Bulgaria
30	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Bahrain
31	0.617.096	HS 800 Sealing Machine	FG	No	JDE	Burundi
32	0.617.096	HS 800 Sealing Machine	FG	No	DIV List	Croatia
33	0.617.096	HS 800 Sealing Machine	FG	No	DIV List	Greece
34	0.617.096	HS 800 Sealing Machine	FG	No	DIV List	Morocco
35	0.617.067	HS 800 Sealing Machine	FG	No	JDE	Andorra
Sheet3

 

[SteveOranjinSteve]

Can you hold my hand through this a little bit? How do I set that up?

 

[SteveOranjinSteve]

Ok, I'm rolling here. I don't know where to go though to enter code into Power Query. How do I do that?

 

[lrobbo314]

Add your original data range as a table to Power Query. Should be under 'Get & Transform' on the 'Data' tab in Excel. Once you've added the table to Power Query, on the Home tab, click on 'Advanced Editor' and paste the code.

Also, make sure the table name matches what's in the code.

e.g. Source = Excel.CurrentWorkbook(){[Name="YOURTABLENAME"]}[Content]