how to identify duplicates in one column based on a common value in another column?

sharphaus In order to see the duplicated rows, select your both column data range. Conditional Formatting/Highlight Cell rules/Duplicate Values/OK

All duplicated rows in your data range should be highlighted in red fill with red text (Important note: the highlighted rows help you to see which rows are duplicated but they should never been used for deletion because if you delete all highlighted rows you are also deleting the 1st occurrence of the duplicates)

If your dataset is small, you can follow the instructions in the other reply to remove the duplicated.

However, because duplicate removal cannot be undone, to ensure that I only remove the duplicated data when my dataset is large, I usually do this way:

1. Assume your data is A2:B7 (A1:B1 is heading)

2. In C1 type Count as the heading and in C2 type: =COUNTIFS(A2:$A$7,A2,B2:$B$7,B2)

Note that you need to make the ending cell of the data range in the above formula as absolute reference with $ sign, whereas the opening cell is relative reference without $ sign)

3. Add filter to the heading of A1:C1

4. Filter column C for any number larger than 1 (i.e. 2, 3, etc) then delete those filtered rows

5. Unfilter column C and now you should expect to see only the number 1 in that column, meaning any row shown is unique.