Unique numbers entered once only

[Guest]

I am having a problem with a client using Excel to record receipts issued
to her clients.
Looking at the data she has entered it appears that she has entered some
receipts twice. Is there any way one can enforce unique integrity on a data
column so that if the operator tries to enter the same number it will be
rejected or ring warning bells ? If you can offer any suggestions it would be
much appreciated.

 

[Guest]

Hi,

Select column A and then

Data|validation

Select custom and enter this formula

=COUNTIF($A:$A,A1)<2

Mike

 

[RobN]

Malcolm,
You could try using Data Validation.
Assuming the data to be entered will be in Column B you should click in the
first row of that data and open the Data Validation Function from the
Menubar and select Custom and then in the Formula field enter:
=1=COUNTIF($B$1:$B$2000,B1)
This assumes the data will go down as far as row 2000 and starts at row 1.
(Change as required.)
Then in the Error Alert tab of the Data Validation Dialog you should enter
something like:
This Invoice Number has already been entered!
Make sure that the Style in this tab is selected to be Stop.

When that part is completed, copy that cell and select all the cells below
it and choose PasteSpecial from the menu Bar and click Validation. This
will apply the Data Validation to all the cells you have selected in column
B.

Rob

 

[Zone]

Could I barge in to ask a question on this? Mike, this works great. Could
you explain how this formula works in Data Validation? I don't get the A1
part. Thanks! James

 

[Guest]

Hi

What it does in A1 is check whether the same value appears elsewhere in
column A but because the whole column is selected when the formula is entered
and because A1 isn't absolute it increments for each cell so the A2 formula
becomes

=COUNTIF($A:$A,A2)<2

This checks if the value in A2 appears in AA on so on. Got it?

Mike

 

[Zone]

Yes, I see. Thank you!
James

Hi

What it does in A1 is check whether the same value appears elsewhere in
column A but because the whole column is selected when the formula is
entered
and because A1 isn't absolute it increments for each cell so the A2
formula
becomes

=COUNTIF($A:$A,A2)<2

This checks if the value in A2 appears in AA on so on. Got it?

Mike