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

SteveOranjin

Board Regular
Joined
Dec 18, 2017
Messages
170
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

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