Highlight Duplicate Values in a Range

An employee imports data (numerical values) into the range A1:T93 each
week. Some values are duplicated within the range, but because the
data is different each time, the values that are duplicated change from
week to week, and she has no easy way to determine which values are
duplicated or how often.

If a number appears in just 2 different cells, it's no problem. But if
a number appears in 3 or more cells, she needs to know that.

Is there some code that can run through the range to determine values
that appears in 3 or more cells and highlight them somehow (ideally
changing the background color of those cells).

As an example, if cells A6, B32 and M45 all contain the number 16, and
cells C9, F13, G83, H2 and R19 contain the number 32, she'd like the
background color of all 8 cells to be changed (say to yellow). (I used
16 and 32 as an example, but the numbers that are duplicated change
from week to week, and usually there are several different numbers
duplicated, not just 2.)

By the way, if finding those in 3 or more cells complicates things too
much vs. just finding those that appear in 2 or more cells, we can live
with that.

Many thanks.

You can just use conditional formatting, something like this...

=COUNTIF(A:A, A1)>2

Highlight all of column A with the cursor in Cell A1. Click
Foramts->Conditional Formats->Formula ... Add the formula and then define
your highlighting...
--
HTH...

Jim Thomlinson


"Paul D. Simon" wrote:

> An employee imports data (numerical values) into the range A1:T93 each
> week. Some values are duplicated within the range, but because the
> data is different each time, the values that are duplicated change from
> week to week, and she has no easy way to determine which values are
> duplicated or how often.
>
> If a number appears in just 2 different cells, it's no problem. But if
> a number appears in 3 or more cells, she needs to know that.
>
> Is there some code that can run through the range to determine values
> that appears in 3 or more cells and highlight them somehow (ideally
> changing the background color of those cells).
>
> As an example, if cells A6, B32 and M45 all contain the number 16, and
> cells C9, F13, G83, H2 and R19 contain the number 32, she'd like the
> background color of all 8 cells to be changed (say to yellow). (I used
> 16 and 32 as an example, but the numbers that are duplicated change
> from week to week, and usually there are several different numbers
> duplicated, not just 2.)
>
> By the way, if finding those in 3 or more cells complicates things too
> much vs. just finding those that appear in 2 or more cells, we can live
> with that.
>
> Many thanks.
>
>

You can do it without the if, but if that makes it easier for you then...

=if(countif(A$1:A$999,A1)>2

but you do need to add the greater than 2 somewhere in the formula...
--
HTH...

Jim Thomlinson


"MDubbelboer" wrote:

>
> pretty simple procedure
>
> conditional formatting will do the trick
>
> select the range you're interested in
> format->conditional->format
> change the dropdown to "formula"
> =if(countif(A$1:A$999,A1),true,false)
> then click the format button, and change the pattern to yellow
>
> This assumes that you highlighted your range starting in cell A1. If
> your active cell is different than A1 you'll have to update the column
> range (i.e. replace A$1:A$999 with B$1:B$999) and the criteria from A1
> to the active cell
>
>
> --
> MDubbelboer
> ------------------------------------------------------------------------
> MDubbelboer's Profile: http://www.excelforum.com/member.php...o&userid=36330
> View this thread: http://www.excelforum.com/showthread...hreadid=563460
>
>

MDubbelboer and Jim: Thank you very much for your responses and
efforts to help.

Your solutions didn't give the desired results, though, even with
modifications. The range where duplications can occur is not just in 1
column but rather covers 20 columns (and 93 rows) in the range A1:T93.

Also, we're not looking for cells that are duplicates of cell A1. The
duplications can occur anywhere within the range and vary from week to
week. In the example I used, cells A6, B32 and M45 all contained the
number 16, and cells C9, F13, G83, H2 and R19 all contained the number
32. Therefore, we'd like those 8 cells highlighted somehow.

However, next week, different cells will contain duplications. For
example, next week, cells B2, C19 and J14 may contain the number 6, and
cells A14, L45 and N62 may contain the number 45, in which case, we'd
want those 6 cells highlighted.

In both examples, I'm only referring to 2 numbers being duplicated. In
reality, we could have 12 different numbers, encompasing 60 cells,
duplicated.

Thanks again for your responses, however. I appreciate your efforts.

I've included some screenshots. Let me know if I've interpreted what you want incorrectly

again take special note of the active cell

the formula countif works as follows
=countif(range,criteria)
in a conditional format because the criteria does not have a $ anywhere it will look in every cell in the range, so:
=if(countif(A$1:A$999,A1)>2
will look in the range $A$1:$A$999 and count how many occurences of each cell there is in that range. It says A1 because that's the active cell. It repeats the procedure for every cell within the range

Thanks very much. Unfortunately, when I clicked on the hyperlink you
provided, I got an "Invalid Attachment Specified" error message.

Also, when I try to modify your formula to cover the desired range of
A1:T93 so that it looks like: =if(countif(A$1:T$93,A1)>2 and click
OK, I get a message telling me there's an error in the formula, and I
can't figure out how to resolve it. Even if I use your formula
verbatim just to test column A instead of the entire range, I still get
an error message. So I must be interpreting something wrong in what
you are trying to tell me.