SOLVED

2 COLUMNS, DUPLICATE ENTRIES

Copper Contributor

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:

 

RAWRAW

 

I'm trying to develop a macro so when it is processed it looks like this:

PROCESSED.PNG

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.

3 Replies
best response confirmed by craygoza92 (Copper Contributor)
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.

Screenshot 2021-12-18 at 07.18.56.png

 

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.

@Riny_van_Eekelen 

 

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:

 

CaptureA.PNG

@craygoza92 The link below would be a good starting point.

https://exceloffthegrid.com/power-query-introduction/ 

1 best response

Accepted Solutions
best response confirmed by craygoza92 (Copper Contributor)
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.

Screenshot 2021-12-18 at 07.18.56.png

 

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.

View solution in original post