How do I compare and highlight the matches from two columns in Excel without replacement?

chrismarince · Jul 16, 2012

I have two columns of data in an Excel spreadsheet. I have to see that every value in Column A has a match in Column B. I understand that two columns can be compared and highlighted in Conditional Formatting using the Match function.
The problem is that the the Match function in Conditional Formatting does not exclude previously matched and highlighted values from the list for future comparisons. For example...

A: 1, 1, 2, 3

B: 2, 3, 1, 0

If using the Match function in Conditional Formatting, both "1" values in Column A would be highlighted because it sees that there is a "1" in column B. I need the columns to match one-to-one. So once a value is found to have a match in the other column, that match is excluded from the comparisons for the rest of the function's execution. If possible, I would prefer doing this entire project without the use of Macros. But if Macros are necessary, I am perfectly fine using them.

Jonmo1 · Jul 16, 2012

Welcome to the board.

Try

=SUMPRODUCT(--(A1:A4=B1:B4))=4

This will return TRUE if all cells in A match their corresponding cell in B.

And instead of having to hard code the count (4), use

=SUMPRODUCT(--(A1:A4=B1:B4))=ROWS(A1:A4)

chrismarince · Jul 16, 2012

Thank you for the reply.

It should be noted that the values that match may not be in the same row. As a matter of fact, 99% of the values won't be in corresponding rows. So when I used your formula in the Conditional Formatting box, nothing seemed to change. I believe it is because of the different order of the values. Is there any way to take this into account?

Thank you!

Marcelo Branco · Jul 16, 2012

Not sure if i unsderstand what you want, but see if this is ok

A B[TABLE="width: 96"]
<colgroup><col style="width: 48pt;" span="2" width="64"> <tbody>[TR]
[TD="class: xl65, width: 64, align: right"]1[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

Select A1:A8 being A1 the active cell

Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format

insert this formula
=COUNTIF($A$1:A1,A1)<=COUNTIF(B:B,A1)

Format button
Fill --> yellow

Ok, Ok

M.

Marcelo Branco · Jul 16, 2012

oops ...no colors

Highlighted cells: A1, A2, A4, A5, A7

M.

Marcelo Branco · Jul 16, 2012

I got this

A B
[TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: yellow, align: right"]1
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
</tbody>[/TABLE]

M.

chrismarince · Jul 16, 2012

Thank you Marcelo!

This is perfect and exactly what I needed!

Thanks to both of you for your help!

Marcelo Branco · Jul 16, 2012

You are welcome and thanks for the feedback!

M.

How do I compare and highlight the matches from two columns in Excel without replacement?

chrismarince

New Member

Excel Facts

Jonmo1

MrExcel MVP

chrismarince

New Member

Marcelo Branco

MrExcel MVP

Marcelo Branco

MrExcel MVP

Marcelo Branco

MrExcel MVP

chrismarince

New Member

Marcelo Branco

MrExcel MVP

Similar threads

Forum statistics

Share this page

How do I compare and highlight the matches from two columns in Excel without replacement?

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock