Highlight specific duplicate values in a list

JinTHR

New Member
Joined
Jan 7, 2015
Messages
13
Hello Mr.Excel Community!
Hope everyone is doing well. I have an issue dealing with formulas within a conditional format. I have two conditions. The first is simple. a condition that highlights any values in a list of values that are duplicates. The second is complicated. I need a condition that will nullify the first condition(highlight duplicates) using a list of values that I do not want highlighted(even when duplicated). An example would be two lists of values. First list has the values: 1,1,2,2,3,3,4,5. Second list has the values: 2,3. What i want to happen is values: 1 and 1 to highlight using a duplicate value condition, but I don't want values: 2,2,3, and 3 to highlight. If anyone can help let me know. If anyone needs further explanation let me know.

Thanks everyone!
Jin K
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,764
Are the list 1 elements in one cell just liked you've typed them, or is the list spread over a range of cells? Same for list 2.
 

JinTHR

New Member
Joined
Jan 7, 2015
Messages
13
Hello Micron,
Thank you for your reply! I should have specified that each value is in a separate cell. So the example would be First list of values are in cells A1:A8. The second list of values are in cells B1:B2. Hope that clarifies!

Thanks,
Jin K
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,764
Well, I've spent a couple of hours trying to make conditional formatting work over a range using COUNTIF(A1:C10,A1)>1 to highlight the duplicates but I'm not good at it so I don't think I can help. I know you can use the duplicates feature to do this automatically, but I figured a formula was required in order to have a chance to negate certain results. I got it to highlight only one of the repeated values, whereas the built in feature highlights them all. Problem is, if I changed a non duplicated value to duplicate another value, the range would not update. Sorry.
 

JinTHR

New Member
Joined
Jan 7, 2015
Messages
13
Hello Micron,
Thank you for your advice. You have truly helped me by giving me new ideas to work with. I will work on figuring this issue out with the new ideas you have given me.
Hope you have a great day!

Thanks again,
Jin
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
Hello Micron,
Thank you for your reply! I should have specified that each value is in a separate cell. So the example would be First list of values are in cells A1:A8. The second list of values are in cells B1:B2. Hope that clarifies!

Thanks,
Jin K
Maybe this formula in Conditional Formatting...

=AND(COUNTIF($A$1:$A$8,A1)>1,ISNA(MATCH(A1,$B$1:$B$2,0)))

Hope this helps

M.
 

Forum statistics

Threads
1,081,726
Messages
5,360,906
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top