Checking for duplicate entries
 

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

Checking for duplicate entries
 

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)

"Daniel- Sydney" wrote in message
...
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

Checking for duplicate entries
 

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

thanks

"Bob Phillips" wrote:

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)

"Daniel- Sydney" wrote in message
...
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

Checking for duplicate entries
 

Yes, using conditional formatting.

Select all items in the row, say B2:EJ2
Goto CF< FormatConditional 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)

"Daniel- Sydney" wrote in message
...
Thanks Bob, is there a way to show me which are duplicates?

thanks

"Bob Phillips" wrote:

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)

"Daniel- Sydney" wrote in
message
...
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