Forum Discussion
Solved
Excel be able to give message when you enter a douplicate number
How can i make my column tell me if i enter the same invoice twice?
One option is to use conditional formatting.
Select the range.
On the Home tab of the ribbon, select Conditional Formatting > Highlight Cells Rules > Duplicate Values...
Click OK.
Alternatively, if you want to avoid entering duplicates:
Let's say you want to apply this to B2:B100.
Select this range. B2 should be the active cell in the selection.
On the Data tab of the ribbon, click Data Validation.
Select Custom from the Allow drop down.
Enter the formula
=COUNTIF($B$2:$B$50,$B2)=1
Activate the Error Alert tab.
Enter an appropriate error message, for example "You have already used this invoice number!"
Click OK.
One option is to use conditional formatting.
Select the range.
On the Home tab of the ribbon, select Conditional Formatting > Highlight Cells Rules > Duplicate Values...
Click OK.
Alternatively, if you want to avoid entering duplicates:
Let's say you want to apply this to B2:B100.
Select this range. B2 should be the active cell in the selection.
On the Data tab of the ribbon, click Data Validation.
Select Custom from the Allow drop down.
Enter the formula
=COUNTIF($B$2:$B$50,$B2)=1
Activate the Error Alert tab.
Enter an appropriate error message, for example "You have already used this invoice number!"
Click OK.
- Work perfect thanks
