CODEWITHSUNDEEP
vbaexcel
Jeff Haas
Jeff Haas

Reputation: 13

Excel: Highlighting duplicates with exact matches

I've got two columns of data, and I would like to use conditional highlighting to find the EXACT matches in both columns. However, using a formula like this one turns up a lot of incorrect matches:

=MATCH(A1,B:B,0)>0

There are a lot of partial matches, so cells are highlighted that I don't want. What I want is the equivalent of the "Match Entire Cell Contents" checkbox in the Find & Replace dialog.

To make this clear, it seems Excel will do partial matches - here is the Find & Replace dialog without "Match Entire Cell Contents" checked, with a sample search for all matches:

Search for all matches, any match

screenshot1

Vs. the dialog with the checkbox ticked and the results:

Search for all matches, match entire cell

screenhot2

But this only applies to find & replace. How can I update the formula above to work the same way, so that only exact matches to what's in a cell are highlighted?

Upvotes: 1

Views: 5092

Answers (2)

Emjamin
Emjamin

Reputation: 21

This isnt quite what is being asked, but this was helpful to me:

To find duplicate records, use Excel's easy-to-use Filter feature as follows:

Select any cell inside the recordset. From the Data menu, choose Filter and then select Advanced Filter to open the Advanced Filter dialog box. Select Copy To Another Location in the Action section. Enter a copy range in the Copy To control. Check Unique Records Only and click OK.

There's more about conditional formatting on this site:

https://www.techrepublic.com/blog/windows-and-office/how-to-find-duplicates-in-excel-245163/

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166835

=NOT(ISERROR(MATCH(A1,B:B,0)))

Regular use of MATCH() - without wildcards - doesn't do partial matches, and MATCH returns an error if there's no hit, not zero

Upvotes: 5

Related Questions