VBA: Check for duplicates in many to one (or two) relationships

[SteveOranjin]

Hello,

I would like to be able to write a macro that looks for duplicates across "Models". What I mean by this specifically is that, there are certain properties that can exist within our products that must of necessity only occur once. However, due to a different concept of what constitutes a model, often times companies that send us data do not respect this mutually exclusive relationship. I'd like some help uncovering these violations and reporting them. Let me explain what I mean:

A product must of necessity have only one occurrence of any given finish across a model. When we say model, we mean "Subaru Impreza". When we refer to a particular model, or a SKU, we mean "Subaru Impreza - Blue". A model may have 1,2,5 or 10 variations in "SKUs" that represent that model. Often times however, due to poor data standardizations that exist within my industry, companies have different concepts of what a model is. As a result, often times, I will load up a brand's products, and will find that a given model of product has duplicates of the same finish. So in certain sections of our website, where users are not supposed to be able to find such duplicates as these, they are often seeing, "Subaru Impreza - Blue, Subaru Impreza - Green, Subaru Impreza - Blue".

An example of this is below:

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Model[/TD]
[TD]SKU[/TD]
[TD]Name[/TD]
[TD]Finish[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]111[/TD]
[TD]111-21[/TD]
[TD]Toilet[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]111[/TD]
[TD]111-22[/TD]
[TD]Toilet[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]111[/TD]
[TD]111-23[/TD]
[TD]Toilet[/TD]
[TD]Purple[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]111[/TD]
[TD]111-24[/TD]
[TD]Toilet[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]222[/TD]
[TD]222-21[/TD]
[TD]Large Toilet[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]222[/TD]
[TD]222-22[/TD]
[TD]Large Toilet[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]222[/TD]
[TD]222-23[/TD]
[TD]Large Toilet[/TD]
[TD]Purple[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]222[/TD]
[TD]222-24[/TD]
[TD]Large Toilet[/TD]
[TD]Blue[/TD]
[/TR]
</tbody>[/TABLE]

What I'd like help writing a macro with is a macro that can spot when there are duplicates of finish type within a model. So in this case, I'm hoping it is possible to create a macro that will spot the duplicates of a blue finish and return a count of those occurrences to the user, so that they can know to go and look for them. Hoping for some help. If there is a good samaritan who is more skilled than me in VBA and is kind enough to help me, then I would be greatly appreciative of it.

Thanks

 

[SteveOranjin]

Hello Steve,

I downloaded the file and opened it. When it tried to open the workbook crashed Excel. When I re-opened the workbook it opened to the "Macro" sheet.

Have you been experiencing these types of problems also? What has been the sequence of the errors you have experienced? What part or parts of the code seem to be failing repeatedly?

Hm.... that's funny, there isn't all that much in it.

It should open in the "Macro" sheet. That is what I have a macro written to do.

The error I am having is the same one as before. So when there is more than one occurrence of a duplicate, it is coming out as, "456789" when it should be "45,67,89"

Please let me know if you need more information

Hope you are well,

Steve

 

[Leith Ross]

Hello Steve,

Found the problem. Column "D" on the "Finish Duplicates" sheet was not formatted properly. The column needs to be formatted as Text to prevent Excel for converting the duplicate row list into a number. For instance, if the macro finds duplicates in rows 102, 103, 104 then the string will be 102,103,104. When this is placed into a cell with General format, Excel will convert this to an actual number. The macro expects a comma separated string of rows and instead receives a single number, which the macro interprets as a single row.

I added a little functionality to the ComboBox on the UserForm. When the ldrop list is visible, you can use the up arrow and down arrow to scroll through the list and examine each row as you go.

Here is the link to the updated workbook... Bravo_Data_Loader_Steve.xls

 

[SteveOranjin]

Hello Steve,

Found the problem. Column "D" on the "Finish Duplicates" sheet was not formatted properly. The column needs to be formatted as Text to prevent Excel for converting the duplicate row list into a number. For instance, if the macro finds duplicates in rows 102, 103, 104 then the string will be 102,103,104. When this is placed into a cell with General format, Excel will convert this to an actual number. The macro expects a comma separated string of rows and instead receives a single number, which the macro interprets as a single row.

I added a little functionality to the ComboBox on the UserForm. When the ldrop list is visible, you can use the up arrow and down arrow to scroll through the list and examine each row as you go.

Here is the link to the updated workbook... Bravo_Data_Loader_Steve.xls

So Lieth,

I don't know if I'm pushing my luck here, but I first of all just want to tell you how amazing this Macro is. I have a question though, and first I believe I should familiarize you with something so you can understand the context of it.

So as I run your macro, if I see a column that has a duplicate finish, I normally delete the row, or I make a change to the data so it can enter our system correctly. However, I've noticed that because the macro does not dynamically update the results, if I decide to delete the row in question, then none of the results are valid anymore. I must then re-run the macro in order to have the results be accurate. This can add additional time, that the macro is designed to save. Although dynamically updating the results would be great, I think there is probably a better and I assume easier solution.

So in cases where there is a duplicate, is it possible to set up the macro to grab the result that is in the SKU column, and copy and paste it into the model column, so then the result that is in the model column is also in the SKU column? I'm going to leave you with a demonstration below.

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Model[/TD]
[TD]SKU[/TD]
[TD]Name[/TD]
[TD]Finish[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]111[/TD]
[TD]111-21[/TD]
[TD]Toilet[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]111[/TD]
[TD]111-22[/TD]
[TD]Toilet[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]111[/TD]
[TD]111-21[/TD]
[TD]Toilet[/TD]
[TD]Blue[/TD]
[/TR]
</tbody>[/TABLE]

So as in the initial table, you'll see that there is a duplicate finish. What I'm hoping for is an addition that will select the result that is in the "SKU" column for the SECOND DUPLICATE (NOT BOTH, so in this case, the result in row 4, NOT the result in row 2.) , and then insert that result into the "Model" column. So then in the cases that there are duplicates, I can effectively wipe out the duplicate by adding the SKU into the model column, thereby making the model unique for that SKU, so it does not create a duplicate. And does not render the results we just generated as invalid. Also, I'm hoping to have this generated by a button that the user clicks, in preference to it happening automatically.

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Model[/TD]
[TD]SKU[/TD]
[TD]Name[/TD]
[TD]Finish[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]111[/TD]
[TD]111-21[/TD]
[TD]Toilet[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]111[/TD]
[TD]111-22[/TD]
[TD]Toilet[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]111-23[/TD]
[TD]111-23[/TD]
[TD]Toilet[/TD]
[TD]Blue[/TD]
[/TR]
</tbody>[/TABLE]
If you are willing to do this, and or can think of an easier way to accomplish the same outcome, then I'd urge you to do that instead.

Either way, thanks again so much Lieth,

Steve

 

[SteveOranjin]

Lieth,

This is the latest sheet, I've added a number of macros since you made this.

http://www.mediafire.com/file/28xzly33568rgxc/Bravo_Data_Loader_Steve_2.xlsb

 

[Leith Ross]

Hello Steve,

It is possible for the macro to remove duplicate entries as they are discovered. Would that be a better option?

 

[SteveOranjin]

Hello Steve,

It is possible for the macro to remove duplicate entries as they are discovered. Would that be a better option?

No, is there a way a button could be set up within the window that pops up that allows you to re-run the macro?

Hope you are well,

Steve

 

[Leith Ross]

Hello Steve,

I changed the macro. Now when it finds a duplicate the SKU places the model number. There is a new button on the UserForm that runs the find duplicates macro again when clicked.

Updated workbook is here: Bravo_Data_Loader_Leith_2.xlsb

 

[SteveOranjin]

Is there a way that I can remove the part of the macro that automatically performs the replace? I'd like to be able to either select in which cases it occurs and which cases it doesn't or to just get rid of it altogether (and then just be able to use the refresh button).

Hope you are well,

Steve

 

[Leith Ross]

Hello Steve,

Sorry, I don't follow you. Cab you give me an example?

 

[SteveOranjin]

Hello Steve,

Sorry, I don't follow you. Cab you give me an example?

So the macro, as is, automatically performs the replacement, without consulting the user. There are cases where if the user sits and puzzles it out for themselves, then they will be able to create the model accurately. Is there a way for us to prevent the macro from automatically populating SKU into the model, and just allow the user to hit refresh (in the macro as it is) so the user can then perform a correction and hit refresh?

Please let me know if you need more information.

STeve