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

's Computer Specifications

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?

--
Regards,
Marvin Hlavac
Toronto, Canada

's Computer Specifications

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

Rgds,
Andy

's Computer Specifications

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>
> --
> Regards,
> Marvin Hlavac
> Toronto, Canada
>
>

's Computer Specifications

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.

--
Regards,
Marvin Hlavac
Toronto, Canada

's Computer Specifications

Thanks Andy,

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

I'm going to look at it.

--
Regards,
Marvin Hlavac
Toronto, Canada

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
formula to show the message that number already entered	=?Utf-8?B?RWxpYW5l?=	Microsoft Excel Worksheet Functions	1	21st Nov 2007 05:59 AM
invoice toolbar for invoice calcuation and assign number	=?Utf-8?B?S2FyZW5Z?=	Microsoft Excel Misc	16	16th Mar 2007 01:02 PM
How do I generate a new invoice number when creating new invoice?	=?Utf-8?B?S2lkZGllV29uZGVybGFuZA==?=	Microsoft Excel Misc	1	15th Mar 2006 04:19 AM
How do I change the invoice number assigned in Invoice template...	=?Utf-8?B?YWtyZXNz?=	Microsoft Excel Misc	1	28th Feb 2005 07:36 PM
Invoice Template with Autoupdate for Invoice Number	Barb Reinhardt	Microsoft Word Document Management	2	10th Dec 2004 09:13 PM