What formula would show me I already entered an invoice number once before?

[Marvin Hlavac]

Hi All,

In column A I enter invoice numbers. Occasionally the same invoice number is
entered by a mistake. How to avoid that? Perhaps in column B a warning would
be displayed beside the invoice number that was entered second time. What
formula could I use?

 

[Andy Brown]

Prevent duplicate entries with Data Validation - see
http://www.contextures.com/xlDataVal07.html#Unique

Rgds,
Andy

 

[Paul B]

Marvin, here is one way, will check for duplicates in A1:A10, change as
needed
=IF(SUMPRODUCT((COUNTIF(A1:A10,A1:A10)>1)+0)>1,"Duplicates exist","")

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[Marvin Hlavac]

Hi Paul,

Marvin, here is one way, will check for duplicates in A1:A10, change as
needed
=IF(SUMPRODUCT((COUNTIF(A1:A10,A1:A10)>1)+0)>1,"Duplicates exist","")

It works and it is exactly what I needed. Thanks a million.

 

[Marvin Hlavac]

Thanks Andy,

Prevent duplicate entries with Data Validation - see
http://www.contextures.com/xlDataVal07.html#Unique

I'm going to look at it.