Warn if already entered

[Pat]

example:

A1 = "car" B1 =
A2 = "lorry" B2 =
A3 = "tractor" B3 =
A4 = "bus" B4 =
A5 = "tractor" B5 = "this item has already been entered"


I need a formula to be entered into col"B" to give the message described.
Any suggestion?

Ta

 

[Ragdyer]

Try this in B1, and copy down as needed:

=IF(COUNTIF($A$1:A1,A1)>1,"This item has already been entered","")

 

[Biff]

Hi!

If you enter the data from the top down all the time:

Leave B1 empty. A1 is the top cell so nothing could have
been "already entered", right?

In B2 enter this formula and copy down as needed:

=IF(COUNTIF(A$1:A2,A2)>1,"This item has already been
entered","")

Biff

 

[Arvi Laanemets]

Hi

You can use custom data validation, to restrict non-unicue entries into
range. When an attempt to enter some value repeatedly, an error message (you
can design it yourself, or leave the standard message unchanged) pops up,
the user is informed about input error, and he/she is allowed to choose or
to retry or to cancel.

P.e. Select the range A1:A100 and then from Data menu Validation. Set Allow:
to Custom, and into Formula field enter
=COUNTIF(A:A,A1)<2
Activate Error Alert tab, and into Title field enter something like:
Input Error!
Into Error Message field enter something like:
Only unique entries are allowed! This entry already exist in column A!
Press OK

Now try to enter any double entry into range A1:A100.