Forum Discussion

Assino960's avatar
Assino960
Copper Contributor
Sep 10, 2020
Solved

Compare two columns and highlight matches

Hello everyone i'm searching how to Compare two columns and highlight matches (1 on 1) there is many different ways but what i want is to highlight one cell if there is only 1 cell that contains ...
excel
Macros and VBA
  • mtarler's avatar
    mtarler
    Sep 10, 2020

    Assino960  well you changed the rules a little. lol.  As for the errors in HansVogelaar method that was because you had an error in the formula in that sheet.  You probably shifted the sheet / target range because instead of A$1:A1 you had A$2:A2 and instead of referencing A2 it was looking at A3, which is what threw everything off.  That all said I think this set of conditional formatting formulas might be what you want:

    =COUNTIF(A$2:A2,A2)<=COUNTIF(B:B,A2)

    =COUNTIF(B$2:B2,B2)<=COUNTIF(A:A,B2)

    see attached sheet.

ExcelExciting's avatar
ExcelExciting
MVP
Sep 10, 2020

Hi Assino960 

 

Please find below the logic which you need to paste the conditional formatting using formula

make sure you select the range correctly. I'm attaching the sample sheet for your ready reference.

 

 

 

 

Logic 01

 

=ISNA(MATCH(B1,$A$1:$A$6,0))

 

 

 

Logic 02

 

=NOT(ISNA(MATCH(B1,$A$1:$A$6,0)))

 

 

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more

  • Assino960's avatar
    Assino960
    Copper Contributor
    Sep 10, 2020

    ExcelExciting 

    Hi.
    thanks for replying 🙂
    i appreciate your help

    But when i use your method on a large data i don't get the result i want, 

    what i want as result that the sum of highlighted cells in A = to the highlighted cells in B

    (if there is 2 cells that contains "x" in column 1, and 3 "x" in column 2, i  want to highlight two cells in each column).

     

Resources