CAN EXCEL TELL ME IF I ENTER THE SAME DATA TWICE?

  • Thread starter Thread starter Accounting
  • Start date Start date
A

Accounting

I want to know if excel can tell if I'm entering the same data twice. For
example I already have an account#01255 on my list then I enter the same
account down the row can excel let me know as I enter this data that I
already have that account # on my worksheet.
 
You want to prevent or flag?

Prevent..................

To implement no-duplicates validation, select the range of cells that you
wish to restrict, and chose Validation from the Data menu. Choose Custom
from the Allow list, and enter the following formula:

=COUNTIF($A$1:$A$50,A1)=1

Flag.........................

This first example will highlight duplicate rows in the range B2:B11. Select
the cells that you wish to test and format, B2:B11 in this example. Then,
open the Conditional Formatting dialog from the Format menu, change Cell
Value Is to Formula Is, enter the formula below, and choose a font or
background format to apply to cells that are duplicates.

=COUNTIF($B$2:$B$11,B2)>1

The formula above, when used in Conditional Formatting, will highlight all
duplicates. That is, if the value 'abc' occurs twice in the list, both
instances of 'abc' will be highlighted. This is shown in the image to the
left, in which all occurrences of 'a' and 'g' are higlighted.


Both of these methods are from Chip Pearson's site.


Gord Dibben MS Excel MVP
 
Prevent could also just be a warning or information by changing the error
alert. This would allow the user to enter the duplicate but it would tell
them that what they are up to may not be appropriate.
 
Back
Top