Excel: highlight duplicate entries

September 30, 2005 at 01:29:13
Specs: Win98se, 512

Dear Everyone,
If I hv a long list of data in Sheet1 & Sheet2 & I need to know if any data in column C of Sheet2 duplicates those in column C of Sheet1, how can I use conditional formatting to highlight the duplicated data?

I've read an excellent reply by Bryan in June 2004 on highlighting duplicate data in the same worksheet. Is it possible to check duplicate data in different worksheets?


See More: Excel: highlight duplicate entries

Report •

September 30, 2005 at 05:46:22

Conditional Formatting will not allow you to look at another sheet, by reference.

There is only one way I know of solving this problem - and that is to name the range that you want to use.

So, on Sheet1, I have named column C "myColumn", and then in Sheet2, I have the conditional formatting formula =NOT(ISERROR(VLOOKUP(C1,myColumn,1,0)))

And thats the (little known but perfectly functional!) only way around it that I know. (This may not be an issues with versions of Excel > Office 2000. I just don't know as this is the latest version I have)

Hope that helped


Report •

September 30, 2005 at 06:16:53

Tom is correct and his formula is pretty slick. It works a charm.

(The referenced June 04 thread provides info for finding duplicates on the same sheet http://computing.net/office/wwwboard/forum/3046.html. See Response #23 for the easy read. I post this for anyone looking for that whom finds this thread. (at least I assume that is the referenced thread))

I am saving this thread so that I can reference Tom's excellent formula in the future.


Report •

October 2, 2005 at 13:56:54

You flatter me, Bryan!


Report •

Related Solutions

October 2, 2005 at 18:13:35

Thanks Tom. Your formula works perfectly well! It will definitely save me a lot of time checking thru the data manually!

Report •

Ask Question