Finding Duplicate Entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list of formulae for about 2000 products. In the table the Product
Name repeats and in the rows to the right of the name are the ingredients. I
can group the data by product, leaving "x" number of blank rows to separate
one column from another.

In that 2000 products, I have maybe 8 that use the same ingredient twice;
once near the beginning, once near the end. I want to be able to identify
those 8 products.

I need some way to consider each product as a discreet group and then
evaluate that group, looking for repeats in the ingredients column. Then I
need someway to highlight or otherwise bring products of this type to my
attention.

Just in case the above was too murky, here's and example of what I'm
looking for:

Product A Step 1 Ingredient-1000
Product A Step 2 Ingredient-8000
Product A Step 3 Ingredient-9000
Product A Step 4 Ingredient-1000
Product A Step 5 Ingredient-2000

I want to spot the two additions of ingredient-1000 (in steps 1 and 4). Can
this be done? Can it be done forcing the macro (or logical argument) to
consider a single grouping of ingredients, a single product, at a time?

Thanks
 
I should add two comments: 1) it's not always the same ingredient that will
repeat within any given formula (the ingredient will vary from formula to
formula) and 2) the ingredient that repeats in a single formula may be used
in making 100+ products in which its use is not repeated. In other words a
simple "Find", isn't applicable.

I picture sorting by ingredients, introducing a break there, then an
argument, =IF (A2=A1, "FLAG", ""); wherein A1 through A"x" are product names
(alphebetized before inserting the break).

I hope someone's got a better idea. :)
 
Venturing some thoughts ..

Assuming source data is in cols A to C, from row2 down
Product A Step 1 Ingredient-1000
Product A Step 2 Ingredient-8000
Product A Step 3 Ingredient-9000
Product A Step 4 Ingredient-1000
Product A Step 5 Ingredient-2000
Click to expand...
etc

and you
.. want to spot the two additions of ingredient-1000 (in steps 1 and 4)
Click to expand...

Place in D2
=IF(SUMPRODUCT(($A$2:A2=A2)*($C$2:C2=C2)*($A$2:A2<>"")*($C$2:C2<>""))>=2,"Dup","")

Copy D2 down to cover the max expected extent of source data. Col D will
flag duplicates based on the data in cols A and C, ie the "Product" &
"Ingredient" cols, if any, as "Dup". We could then select col D, and do a
Data > Filter > Autofilter to filter out all lines with "Dup" for closer
inspection ..

---
 
Back
Top