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

 

[Leith Ross]

Hello Steve,

Since you can have a large amount of data, creating a large number of hyperlinks could make the macro very slow. But, I have a few other ideas about streamlining the process.

 

[Leith Ross]

Hello Steve,

I added quite a bit to this new version. The "Finish Duplicates" sheet is now hidden. It is now used by a UserForm to list the information on the sheet in a ListBox and breaks down the duplicate rows in ComboBox. Selecting a line in the ListBox will automatically list all of the duplicate rows in the ComboBox. You just click on a row number in the ComboBox and click the "Examine Row" button and you will be taken to that entry on the "Data Sheet". Have a look an let me know what you if this works for you or not.

Bravo Data Loader 3.xlsb

 

[SteveOranjin]

Hello Steve,

I added quite a bit to this new version. The "Finish Duplicates" sheet is now hidden. It is now used by a UserForm to list the information on the sheet in a ListBox and breaks down the duplicate rows in ComboBox. Selecting a line in the ListBox will automatically list all of the duplicate rows in the ComboBox. You just click on a row number in the ComboBox and click the "Examine Row" button and you will be taken to that entry on the "Data Sheet". Have a look an let me know what you if this works for you or not.

Bravo Data Loader 3.xlsb

Hey Leith,

I'm having a couple of problems with it.

1.) If there is more than one duplicate, it treats each separate listing as 1 listing. So, "73,23,42" is interpreted as "732342" and it takes me to that line.
2.) This might be more of an advice thing than anything else, but when I open your sheet, and run one of my older macros, your sheet opens my older version of the sheet and runs the macro there instead. I've got a number of macros in there, that I still need to preserve. If I delete all of my older versions and have just the one you provided in your attachment, will that problem cease?

Besides that, I can't believe how great this thing is. Wow! How long did it take you to become that good at VBA? Thank you so much!

Steve

 

[Leith Ross]

Hello Steve,

This will fix the problem #1 . Sometimes using Split directly in a For Each loop does not always work correctly, depending on the users system and setup. This method is more robust and I should have used it initially. Here is the updated code for ListBox1's Click event. Replace the code in UserForm1 with the code shown here.

Private Sub ListBox1_Click()


    Dim Data As Variant
    
        If ListBox1.ListIndex = 0 Then Exit Sub
        
        ComboBox1.Clear
        
        Data = Split(ListBox1.List(ListBox1.ListIndex, 3), ",")
        
        For Each Row In Data
            ComboBox1.AddItem Row
        Next Row
        
        ComboBox1.Value = ComboBox1.List(0)
        ComboBox1.SetFocus
        SendKeys "+{F4}"
        
End Sub

As for question #2 , I am not really sure which old macros and sheets you are referring to. Are these formulas on the sheet or VBA macros. Can you give me an example?

To answer you question about VBA, I am still learning even after 20 years.

 

[SteveOranjin]

Hello Lieth,

(All of this is true minus any additions you have made in the above post.)

So the scenario we're discussing is as follows:

1.) I have two versions of "Bravo Data Loader". One of them is called, "Bravo_Data_Loader_3" (yours) and "Bravo_Data_Loader" (which we'll call mine, which is all the macros I have written into it.)

2.)So when I run, say, "Clear_Data" Macro (I can't recall what it is named off the top off my head) in "Bravo_Data_Loader_3", instead of performing the correct procedure, which is to clear "Data Sheet", it opens "Bravo_Data_Loader" and runs the clear page macro in excel document, in the "Data Sheet" tab there. It is not performing the operation in "Bravo_Data_Loader_3" - that is in the excel document in which I triggered the operation to perform in. This was not happening before . Additionally, if I run the "Clear_Data" macro in "Bravo_Data_Loader" then the "Clear_Data" macro runs in the correct document.

I apologize for the redundancy there. On the internet like this I try to explain things as clearly as possible, and I find the best way to do that is explaining one thing multiple ways.

Hope you are well,

Steve

 

[SteveOranjin]

Lieth,

2#) I was able to solve that problem. The Macro wasn't working when I selected it in the Ribbon I was using. I guess I'll just have to setup that ribbon again.

1.) I am unable to find the macro, "Private Sub ListBox1_Click()". Can I get a hint on how to locate it?

Steve

 

[Leith Ross]

Hello Steve,

Glad you got it solved. I had no idea you were calling this from the Ribbon.The macro ListBox1_Click() is in UserForm1.

 

[SteveOranjin]

Hello Steve,

Glad you got it solved. I had no idea you were calling this from the Ribbon.The macro ListBox1_Click() is in UserForm1.

Thanks so much leith. That reminds me, one more quick question. Is there a way that I can save the ribbon so it carries with the worksheet so I can send it to someone else and have it be present?

What do I do to do that?

Hope you are well,

Steve

 

[SteveOranjin]

Hello Steve,

Glad you got it solved. I had no idea you were calling this from the Ribbon.The macro ListBox1_Click() is in UserForm1.

Hi lieth. Hope you are well.

Is there a way I can persuade you to take one more look at this for me? The same issue is occuring again. I left the data in the file for you to look at.

Again I appreciate very much the time you've taken,

http://www.mediafire.com/file/tnp8dcsgl4u096p/Bravo_Data_Loader_Lieth.xlsb

Steve

 

[Leith Ross]

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?