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]

How do I find out what the table name is? I was just about to post that. That's how far I got.

 

[SteveOranjinSteve]

This is literally the fourth time I've used power query.. used it twice a year ago I think....

 

[SteveOranjinSteve]

This is literally the fourth time I've used power query.. used it twice a year ago I think....

Does it have to match "='Ooriginal Sheet" ?

 

[lrobbo314]

When you add your table and then get into the advanced editor, you should see a line that looks like this. It has the correct table name in there already.

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

When you paste the code in just make sure that that "Table1" is in the pasted code. I believe my original post was Table6.

 

[alansidman]

Steve - Look at the link in my signature block on using Mcode and PQ