check for repeated data

  • Thread starter tikchye_oldLearner57
  • Start date
T

tikchye_oldLearner57

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
 
J

Joel

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

Max

One way using conditional formatting ..

Select A1:F2 (ensure A1 is active cell*)
Click Format>Conditional 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
 
M

Max

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

Select A1:F2 (ensure A1 is active cell, as before)
Click Data>Validation
In the settings tab:
Allow: Custom
Formula: =COUNTIF($A$1:$F$2,A1)>1
Click OK
 
T

tikchye_oldLearner57

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

tikchye_oldLearner57

thanks Max , very interesting and good :)

thanks again Max
and
to community as well
:)
 
Top