• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

if column A contains a duplicate, highlight row with "x" in column B

L

lg215

New Member
Hello!

Im so stuck and struggling, I think to even ask the right question!

I have about 5,000 rows. Many have a duplicate ID in column A. I want to highlight the rows that have "X" in column B, if column A is a duplicate.

Column A Column B
1223 X
1223 T
1223 S
1224 S
1225 X

Ideally what I would like is every ID to be listed/highlighted only once with preference given (in the instance of duplicates) to rows containing "x". However if an ID only occurs once in column A then I still want that row (in this example that would include 1224, as it is not a duplicate but column B does not contain "X").

Any help would be appreciated

thanks

Laura
 
Hi Laura,

To clarify, are you wanting to highlight the duplicates, or the unique values? You started by talking about duplicates, specifically duplicates with a "X" in col B, but then you mentioned highlighting the 1224, which is not a duplicate. Please advise.
 
Hi Luke,

Thanks for replying. Yes it's not straight forward.
I have a sheet with many duplicate ID's in Column A and in column B I have list of items (in my example I have labelled these just as letters e.g X).

The end result I am trying to achieve is that column A contains no duplicates. (This bit I know how to do). The bit I'm struggling with is that I would like some preference attached to which duplicate is highlighted based on the contents of column B.

1224 is not a duplicate and does not contain "x" in column B. It is an order of preference I am struggling with. So that if there is a duplicate in column A, look in column B and highlight the one with the X. If there is no duplicate ID in column A highlight that row anyway.

Or the reverse which probably (hopefully) makes more sense: If there is a duplicate in column A, look in column B and highlight the one withOUT the X.

Thank you

Laura
 
Or the reverse which probably (hopefully) makes more sense: If there is a duplicate in column A, look in column B and highlight the one withOUT the X.
Click to expand...
Ah, that makes more sense.

Select range A2:B1000 (or however big your range is) and apply a conditional formatting rule of:
=AND(COUNTIF($A:$A,$A2)>=2,$B2<>"X")
 
Luke you genius! Thanks for helping me understand my question and then providing the answer! That has done exactly what I needed.

Thank you
 
You must log in or register to reply here.
Back
Top