Highlight specific duplicate values in a list

[JinTHR]

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]

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]

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, 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]

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]

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.