Dec 17 2021 11:10 AM
Good afternoon, this is my first post;
I've got 2 columns, 1 with product names (Sku) and 1 with product locations (Location). Products have different locations and when running a report it gives me the information in the following format:
I'm trying to develop a macro so when it is processed it looks like this:
Basically removing the multiple entries from the SKU side and inserting the SKU locations one per column to the right of the SKU.
Thanks for the help.
Dec 17 2021 10:22 PM
Solution@craygoza92 I would go for a Power Query solution as demonstrated in the attached file.
Converted a small portion of your list into a structured table and added a column to count the number of occurrences for any specific SKU. This is used in PQ to split the locations per SKU into the correct number of columns. You can do that within PQ as well, but this way it's easier to follow.
Add your real data into the blue table (called Table1). The formula in the Count column should fill down automatically. Press Refresh All on the Data ribbon. The green table should update instantly. See if this works for you.
Dec 20 2021 10:58 AM - edited Dec 20 2021 11:12 AM
Edit 1 - Nevermind, it worked, thanks!.
Edit 2 - Is there any guide that you can link me to so I can understand how this was achieved? Thanks again.
Thanks for your reply, tried using your sample file but I cannot get it to work, nothing happens when I hit refresh all, i get these windows:
Dec 20 2021 12:34 PM
@craygoza92 The link below would be a good starting point.
Dec 17 2021 10:22 PM
Solution@craygoza92 I would go for a Power Query solution as demonstrated in the attached file.
Converted a small portion of your list into a structured table and added a column to count the number of occurrences for any specific SKU. This is used in PQ to split the locations per SKU into the correct number of columns. You can do that within PQ as well, but this way it's easier to follow.
Add your real data into the blue table (called Table1). The formula in the Count column should fill down automatically. Press Refresh All on the Data ribbon. The green table should update instantly. See if this works for you.