Highlighting duplicates

Hi guys, I know this question has probably been asked 1000 times before, but not of the solutions I have seen seem to work for me.

I have a table with several columns and rows. Column E contains the reference number for each row. Essentially, E2 to E1500 has a load of reference numbers.

To the side, not part of this table, I then have a general list of reference numbers with no formatting on it at all. This list ranges from N4 to N600. What I need to do is check every reference number in my table to the list on the side, and if it matches anything in the list for it to be highlighted. So if anything from E2 to E1500 matches anything in N4 to N600, highlight it.

Is this possible with a formula or macro?


Thanks

If you want to avoid a formula to compare the data, it'd be easiest to just download an excel add-in like kutools that provides this functionality with a couple quick clicks.

http://www.extendoffice.com/product/...d-columns.html

I did actually try kutools, but whenever I clicked on anything it gave me a kind of warning popup with no information on it whatsoever. So not to sure if its working for me.

demo.xlsx
The problem I have with highlight cell rules, it only shows duplicates in column E. It seems to ignore comparing them against my other list.

And with the formula =COUNTIF($N$4:$N$600,E2)>0 , I place this in say K2 (which is an empty column) and then I drag it all the way down to K1500. Every single value displayed in K is false though.

I have attached an example to demonstrate.

Thanks

The formula I provided is intended to be a CF rule, not a cell formula. However, maybe your demo is just to test out the formula. In your demo file, none of the values in column A are found in column F. So of course it always returns FALSE.

Your original post didn't mention needing to parse through multiple values in the same cell, which makes this a lot more complicated.

Hi, even as a CF rule, nothing is highlighted. I am slightly confused when you say that none of the values in column A are found in column F. For instance, F2 is found in A260, so this is a match. If I do need to parse through multiple values in the same cell, what would be my options?

Cheers

The string in F2 has a space at the end. The string in A260 does not. Therefore no match. If you do a find & replace on your entire sheet to eliminate all spaces, you will get 286 matches.

Parsing through multiple values in the same cell means either:

• Additional "helper" columns that break out the values. The formula would be a little complicated because sometimes the separator is "," and sometimes it's "/" and sometimes it's both.
• VBA code to do the whole thing.

I am better at VBA than I am at complicated formulas so that's where I would lean. Others might be better ideas.