Checking for duplicate entries

[Guest]

Hi

I have Excel 2002.

I have a spreadsheet with a number of ingredients in the first column and
the next 130 columns have formulations for various products made on the
ingredients in the first column, these formulation all add to 100.

Is there a way i can automatically check for any duplicate formulations?

Thanks

Daniel

 

[Bob Phillips]

Insert a new column in B and add a formula of

=SUMPRODUCT((C12:EK12<>"")/COUNTIF(C12:EK12,C12:EK12&""))=COUNTA(C12:EK12)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks Bob, is there a way to show me which are duplicates?

thanks

 

[Bob Phillips]

Yes, using conditional formatting.

Select all items in the row, say B2:EJ2
Goto CF< Format>Conditional Formatting
Change Condition1 to Formula Is
Add =COUNTIF($B2:B2,B2)>1 (note the use of $)
Click Format
Select Pattern
Choose a highlighting colour
OK

This will highlight second and further instances of the duplicates. If you
want all including the first, use a formula of

=COUNTIF($B2:$EJ2,B2)>1


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)