Warning while entering duplicate values in a cell

[Guest]

Hi there guys

Was just wondering - is there a way to get a warning message pop up (or any
kind of warning) if a value is entered in a cell that already exists in
another cell in that column earlier/later? This will enable me to eliminate
entering duplicate values in a cell in a particular column...

Thanks muchly.

Raj

 

[Guest]

You can use Data Validation to prevent entry of duplicate values.

Select your column (I'll use Column A for this example)
From the Data Menu, select "Validation"
In the Allow field, select "Custom"
Enter the formula: =COUNTIF(A:A,A1)=1

You can also set custom messages to display if duplicates are entered.

Note, this will only prevent manual entry of data from being duplicated. It
will not find existing duplicates or prevent Copy/Pasting duplicate data.

Another option would be to use Conditional Formatting to highlight cells
that contain duplicates. This would allow you to find existing duplicates
and catch Copy/Pasted data. The same formula above could be used there with
one little change. =COUNTIF(A:A,A1)>1

HTH,
Elkar

 

[Guest]

Thanks Elkar...

You can use Data Validation to prevent entry of duplicate values.

Select your column (I'll use Column A for this example)
From the Data Menu, select "Validation"
In the Allow field, select "Custom"
Enter the formula: =COUNTIF(A:A,A1)=1

You can also set custom messages to display if duplicates are entered.

Note, this will only prevent manual entry of data from being duplicated. It
will not find existing duplicates or prevent Copy/Pasting duplicate data.

Another option would be to use Conditional Formatting to highlight cells
that contain duplicates. This would allow you to find existing duplicates
and catch Copy/Pasted data. The same formula above could be used there with
one little change. =COUNTIF(A:A,A1)>1

HTH,
Elkar