check for repeated data

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

thanks Max , very interesting and good

thanks again Max
and
to community as well

 

[Max]

Welcome, tikchye.