Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting of duplicates in a range
I went already through a lot of the threats in this discussion group but
could not find an answer to my problem. I use Excel2007. Within a range of 4 columns and several rows I need to identify the nbrs that appear duplicated in the different columns. Each # will be unique in each column, but may appear up to 4x in the range like in the given example. A B C D 1) 31 34 34 22 2) 34 31 22 34 3) 27 42 42 6 4) 48 22 9 42 5) 12 48 31 1 6) 42 5 5 9 Furthermore the highlights of the duplicates shall be in different background colors like: red = 4x (f.e. #34) yellow = 3x (#22 & #31) green = 2x (#5 & #9) blank = 1x Any help is appreciated. Tks Gerhard |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting of duplicates in a range
Home tab Conditional Formatting New Rule use formula to determine which
cells to format =COUNTIF($A$1:$D$6,A1)=4 format as red =COUNTIF($A$1:$D$6,A1)=3 format as yellow and so on... "gerhard" wrote: I went already through a lot of the threats in this discussion group but could not find an answer to my problem. I use Excel2007. Within a range of 4 columns and several rows I need to identify the nbrs that appear duplicated in the different columns. Each # will be unique in each column, but may appear up to 4x in the range like in the given example. A B C D 1) 31 34 34 22 2) 34 31 22 34 3) 27 42 42 6 4) 48 22 9 42 5) 12 48 31 1 6) 42 5 5 9 Furthermore the highlights of the duplicates shall be in different background colors like: red = 4x (f.e. #34) yellow = 3x (#22 & #31) green = 2x (#5 & #9) blank = 1x Any help is appreciated. Tks Gerhard |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting of duplicates in a range
Thank u very much. This works exactly as I wanted. Tks again.
"Teethless mama" wrote: Home tab Conditional Formatting New Rule use formula to determine which cells to format =COUNTIF($A$1:$D$6,A1)=4 format as red =COUNTIF($A$1:$D$6,A1)=3 format as yellow and so on... "gerhard" wrote: I went already through a lot of the threats in this discussion group but could not find an answer to my problem. I use Excel2007. Within a range of 4 columns and several rows I need to identify the nbrs that appear duplicated in the different columns. Each # will be unique in each column, but may appear up to 4x in the range like in the given example. A B C D 1) 31 34 34 22 2) 34 31 22 34 3) 27 42 42 6 4) 48 22 9 42 5) 12 48 31 1 6) 42 5 5 9 Furthermore the highlights of the duplicates shall be in different background colors like: red = 4x (f.e. #34) yellow = 3x (#22 & #31) green = 2x (#5 & #9) blank = 1x Any help is appreciated. Tks Gerhard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting for Duplicates | Excel Worksheet Functions | |||
Duplicates in Conditional Formatting | Excel Discussion (Misc queries) | |||
Locate duplicates in a work book by using conditional formatting | Excel Worksheet Functions | |||
Conditional Formatting to find duplicates | Excel Discussion (Misc queries) | |||
locate duplicates by sing conditional formatting--doesn't work for | Excel Discussion (Misc queries) |