check for repeated data
 

hi community

is there a way to do checking for repeated data entry in a range of cells,

A B C D E F

1 10 5 6 11 3 9

2 5 13 2 11 1 0

as an example above, i want to check for repeated data in the cell range and
have it highlighted if more than 1 cell are detected

any help would be much appreciated

thanks community
--
oldLearner57

check for repeated data
 

conditional formating will do what you want.

Select cell A1 - this is same cell as 2nd parameter in Countif below
Format Menu - Conditional formating
change Condition to formula is

Enter this formula. Note where the dollar sign are located.
=IF(COUNTIF($A$1:$G$2,A1)1,TRUE,FALSE)
Select format - Pattern and chose any color.

Now you have to copy the conditional formating to all the cells in the range
Again select cell A1. Press Paint brush on toolbar. Highlight all cells
in range you want to compare.

"tikchye_oldLearner57" wrote:

hi community

is there a way to do checking for repeated data entry in a range of cells,

A B C D E F

1 10 5 6 11 3 9

2 5 13 2 11 1 0

as an example above, i want to check for repeated data in the cell range and
have it highlighted if more than 1 cell are detected

any help would be much appreciated

thanks community
--
oldLearner57

check for repeated data
 

One way using conditional formatting ..

Select A1:F2 (ensure A1 is active cell*)
Click FormatConditional Formatting
Under Condition 1, make the settings:
Formula is: =COUNTIF($A$1:$F$2,A1)1
Click Format button Patterns tab Blue? OK
Click OK at the main dialog

*means select A1 first, then drag to F2 to select A1:F2
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"tikchye_oldLearner57" wrote:
hi community

is there a way to do checking for repeated data entry in a range of cells,

A B C D E F

1 10 5 6 11 3 9

2 5 13 2 11 1 0

as an example above, i want to check for repeated data in the cell range and
have it highlighted if more than 1 cell are detected

any help would be much appreciated

thanks community
--
oldLearner57

check for repeated data
 

And if your intent is to prevent duplicate data entry ..

Select A1:F2 (ensure A1 is active cell, as before)
Click DataValidation
In the settings tab:
Allow: Custom
Formula: =COUNTIF($A$1:$F$2,A1)1
Click OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"tikchye_oldLearner57" wrote:
hi community

is there a way to do checking for repeated data entry in a range of cells,

A B C D E F

1 10 5 6 11 3 9

2 5 13 2 11 1 0

as an example above, i want to check for repeated data in the cell range and
have it highlighted if more than 1 cell are detected

any help would be much appreciated

thanks community
--
oldLearner57

check for repeated data
 

the method works well and simple
much appreciated :)
thanks again :) Joel
also to community - thanks!
--
oldLearner57


"Joel" wrote:

conditional formating will do what you want.

Select cell A1 - this is same cell as 2nd parameter in Countif below
Format Menu - Conditional formating
change Condition to formula is

Enter this formula. Note where the dollar sign are located.
=IF(COUNTIF($A$1:$G$2,A1)1,TRUE,FALSE)
Select format - Pattern and chose any color.

Now you have to copy the conditional formating to all the cells in the range
Again select cell A1. Press Paint brush on toolbar. Highlight all cells
in range you want to compare.

"tikchye_oldLearner57" wrote:

hi community

is there a way to do checking for repeated data entry in a range of cells,

A B C D E F

1 10 5 6 11 3 9

2 5 13 2 11 1 0

as an example above, i want to check for repeated data in the cell range and
have it highlighted if more than 1 cell are detected

any help would be much appreciated

thanks community
--
oldLearner57

check for repeated data
 

thanks Max , very interesting and good :)

thanks again Max
and
to community as well
:)
--
oldLearner57


"Max" wrote:

And if your intent is to prevent duplicate data entry ..

Select A1:F2 (ensure A1 is active cell, as before)
Click DataValidation
In the settings tab:
Allow: Custom
Formula: =COUNTIF($A$1:$F$2,A1)1
Click OK
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"tikchye_oldLearner57" wrote:
hi community

is there a way to do checking for repeated data entry in a range of cells,

A B C D E F

1 10 5 6 11 3 9

2 5 13 2 11 1 0

as an example above, i want to check for repeated data in the cell range and
have it highlighted if more than 1 cell are detected

any help would be much appreciated

thanks community
--
oldLearner57

check for repeated data
 

Welcome, tikchye.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On Mar 8, 10:04 am, tikchye_oldLearner57
wrote:
thanks Max , very interesting and good :)

thanks again Max
and
to community as well
:)
--
oldLearner57