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

SteveOranjinSteve

Board Regular
Joined
Nov 18, 2019
Messages
78
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

ProductSKU DescriptionSpare Part(SP)/Finished Good (FG)In SAP?Name (New - RFI list)JDEDIV list
0.617.096HS 800 Sealing MachineFGNoCroatia, Greece, Morocco, Spain, South AfricaAndorra, 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, BurundiCroatia, Greece, Morocco
0.617.067HS 800 Sealing MachineFGNoAndorra, 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.


ProductSKU DescriptionSpare Part/Finished GoodIn SAPListCountry
0.617.096HS 800 Sealing MachineFGNoRFICroatia
0.617.096HS 800 Sealing MachineFGNoRFIGreece
0.617.096HS 800 Sealing MachineFGNoRFIMorocco
0.617.096HS 800 Sealing MachineFGNoRFISpain
0.617.096HS 800 Sealing MachineFGNoRFISouth Africa
0.617.096HS 800 Sealing MachineFGNoJDEAndorra

Is this something that is possible?

Please see Copy of Comparison - Pivot 6.8.2.1.xlsm

Steve
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
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
 
Upvote 0
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.

ProductSKU DescriptionSpare Part/Finished GoodIn SAPListCountry
0.617.096HS 800 Sealing MachineFGNoRFICroatia
0.617.096HS 800 Sealing MachineFGNoRFIGreece
0.617.096HS 800 Sealing MachineFGNoRFIMorocco
0.617.096HS 800 Sealing MachineFGNoRFISpain
0.617.096HS 800 Sealing MachineFGNoRFISouth Africa
0.617.096HS 800 Sealing MachineFGNoJDEAndorraA
0.617.096HS 800 Sealing MachineFGNoJDEAndorraA
0.617.096HS 800 Sealing MachineFGNoJDEAndorraB
0.617.096HS 800 Sealing MachineFGNoDIVAndorraB

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.
 
Upvote 0
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

 
Upvote 0
Here is a way to do it with Power Query.

Code:
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
ABCDEFG
1ProductSKU DescriptionSpare Part(SP)/Finished Good (FG)In SAP?Name (New - RFI list)JDEDIV List
20.617.096HS 800 Sealing MachineFGNoCroatia, Greece, Morocco, Spain, South AfricaAndorra, 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, BurundiCroatia, Greece, Morocco
30.617.067HS 800 Sealing MachineFGNoAndorra, 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
5ProductSKU DescriptionSpare Part(SP)/Finished Good (FG)In SAP?ListCountry
60.617.096HS 800 Sealing MachineFGNoRFICroatia
70.617.096HS 800 Sealing MachineFGNoRFIGreece
80.617.096HS 800 Sealing MachineFGNoRFIMorocco
90.617.096HS 800 Sealing MachineFGNoRFISpain
100.617.096HS 800 Sealing MachineFGNoRFISouth Africa
110.617.096HS 800 Sealing MachineFGNoJDEAndorra
120.617.096HS 800 Sealing MachineFGNoJDEUnited Arab Emirates
130.617.096HS 800 Sealing MachineFGNoJDEAfghanistan
140.617.096HS 800 Sealing MachineFGNoJDEAntigua and Barbuda
150.617.096HS 800 Sealing MachineFGNoJDEAlbania
160.617.096HS 800 Sealing MachineFGNoJDEArmenia
170.617.096HS 800 Sealing MachineFGNoJDENetherlands Antilles
180.617.096HS 800 Sealing MachineFGNoJDEAngola
190.617.096HS 800 Sealing MachineFGNoJDEArgentina
200.617.096HS 800 Sealing MachineFGNoJDEAustria
210.617.096HS 800 Sealing MachineFGNoJDEAustralia
220.617.096HS 800 Sealing MachineFGNoJDEAruba
230.617.096HS 800 Sealing MachineFGNoJDEAzerbaijan
240.617.096HS 800 Sealing MachineFGNoJDEBosnia and Herzegovina
250.617.096HS 800 Sealing MachineFGNoJDEBarbados
260.617.096HS 800 Sealing MachineFGNoJDEBangladesh
270.617.096HS 800 Sealing MachineFGNoJDEBelgium
280.617.096HS 800 Sealing MachineFGNoJDEBurkina Faso
290.617.096HS 800 Sealing MachineFGNoJDEBulgaria
300.617.096HS 800 Sealing MachineFGNoJDEBahrain
310.617.096HS 800 Sealing MachineFGNoJDEBurundi
320.617.096HS 800 Sealing MachineFGNoDIV ListCroatia
330.617.096HS 800 Sealing MachineFGNoDIV ListGreece
340.617.096HS 800 Sealing MachineFGNoDIV ListMorocco
350.617.067HS 800 Sealing MachineFGNoJDEAndorra
Sheet3
 
Upvote 0
Ok, I'm rolling here. I don't know where to go though to enter code into Power Query. How do I do that?
 
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top