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

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.

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

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

On Wed, 16 Sep 2009 12:46:01 -0700, Accounting
wrote:

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.

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

Forgot Chip's URL to Topic Index

http://www.cpearson.com/excel/topic.aspx


Gord

On Wed, 16 Sep 2009 12:53:57 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Wed, 16 Sep 2009 12:46:01 -0700, Accounting
wrote:

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.

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

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.
--
HTH...

Jim Thomlinson


"Gord Dibben" wrote:

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

On Wed, 16 Sep 2009 12:46:01 -0700, Accounting
wrote:

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.

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

True


Gord

On Wed, 16 Sep 2009 13:28:02 -0700, Jim Thomlinson
wrote:

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.