For each value in range, does it exist in this other range?

[evergladesfl]

I can't seem to figure this out. I have two columns, and each cell
contains what should be a unique value. However, I want to be sure
that there are no duplicates of any cell value from column A to the
cell values in column B. How do I go about this?

Thanks

 

[Tezza]

Use the following formula in column C and copy down for each value to be
checked in column B:

=COUNTIF($A$1:$A$28, "="&B1)

This counts the number of values in A1:A28 that are equal to the value in
B1. If all values are unique the formula should return a zero

Terry Rees

 

[evergladesfl]

Thank you very much - exactly what I needed.

Use the following formula in column C and copy down for each value to be
checked in column B:

=COUNTIF($A$1:$A$28, "="&B1)

This counts the number of values in A1:A28 that are equal to the value in
B1. If all values are unique the formula should return a zero

Terry Rees

 

[iliace]

Perhaps even more user-friendly, go to the first cell of your range and
enter the formula in conditional format - absolute references for
range, relative for criteria. You can do something like "Formula Is"
=COUNTIF(DataRange,A1)>1 in cell A1, then format-paste for the entire
range. DataRange is a name referring to everywhere you want to look
for duplicate values.

You can use this technique to highlight multiple items; for instance,
one color for two occurences, another color for 3, and yet another for
4+.