How to compare 2 columns and find duplicates and highlight in one column only?

  1. ML28
    ML28
    Hi All,

    I'm stuck, so would love it if someone can assist. See example of what I need to do:

    If we have column A with number values, and then, we have column C with many number values. Target: we want to compare both columns, find the duplicates that exist only in column A when compared to column C, and then highlight those in column C.


    Column A Column C

    1 1
    2 2
    3 3
    4 4
    5
    6
    7

    So, in the above example, we would only like in Column C, 1 thru 5 highlighted since they are duplicate of Column A. Anyone know how to do this??
  2. smajic
    smajic
    If you are using Office 2013, you could try using conditional formatting in Column C. I have assumed your data starts at row 1 for the purpose of this response.

    Highlight your first row in Column C, choose Conditional Formatting from the Home tab.

    Choose New Rule, and the option Use a formula to determine which cells to format.

    Then type the formula =COUNTIF(A:A,C1)>1 into the box called Format values where this formula is true. Click the format button and choose your desired format. then click on OK. This will apply the format to the first cell in column C (C1 - change the C1 in the formula to C2 if your data starts at row 2.)

    Finally use the format painter button to copy the conditional formatting to the other cells in column C.

    I hope this is clear and that it solves your issue.
  3. PlusX.me
    PlusX.me
    For me it worked by creating a rule with the formula =VLOOKUP(C1;A:A;1;0)>0
Results 1 to 3 of 3

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1