Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight Duplicate Values in a Range
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight Duplicate Values in a Range
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. I your active cell is different than A1 you'll have to update the colum range (i.e. replace A$1:A$999 with B$1:B$999) and the criteria from A to the active cel -- MDubbelboe ----------------------------------------------------------------------- MDubbelboer's Profile: http://www.excelforum.com/member.php...fo&userid=3633 View this thread: http://www.excelforum.com/showthread.php?threadid=56346 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight Duplicate Values in a Range
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight Duplicate Values in a Range
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight Duplicate Values in a Range
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 +-------------------------------------------------------------------+ |Filename: Conditional Format - Result.jpg | |Download: http://www.excelforum.com/attachment.php?postid=5067 | +-------------------------------------------------------------------+ -- MDubbelboer ------------------------------------------------------------------------ MDubbelboer's Profile: http://www.excelforum.com/member.php...o&userid=36330 View this thread: http://www.excelforum.com/showthread...hreadid=563460 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight Duplicate Values in a Range
Thanks very much. Unfortunately, when I clicked on the hyperlink you
provided, I got an "Invalid Attachment Specified" error message. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight Duplicate Values in a Range
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identifying duplicate values in a range | Excel Discussion (Misc queries) | |||
Duplicate values in range of fields | Excel Discussion (Misc queries) | |||
How to Highlight Two Rows With Two Columns of Duplicate Values | Excel Worksheet Functions | |||
how do i prevent duplicate values in a range of validated cells? | Excel Discussion (Misc queries) | |||
is there a formula that remove duplicate values from a range | Excel Worksheet Functions |