Forum Discussion
Compare two columns and highlight matches
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.
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, and also tehere is a lot of matches that are not highlighted.
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).
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.