Isolating Duplicates Among 2 Large Pieces of Data

[Wonton]

Hello all,

I am trying to accomplish a simple task that I remember learning in my Excel courses from college, but for some reason I can not remember how to do it. I tried searching but unfortunately I do not know the proper name of what I am trying to accomplish.

I have two data sets in excel. List 1 contains 500 names where each item is in its own cell, lets say cells A1-A500. List 2 contains 300 names, say in cells C1-C300. I would like to run a search on List 1 using all 300 names from List 2 as search terms, and somehow indicate (highlight/bold/underline) those names that were found in List 1 from List 2. Essentially I am trying to isolate anything in list 2 that is not in list 1 or vice versa.

I do not believe I need any VBA coding to do this, and remember it was quite simple. I would appreciate if anyone can guide me through how I might perform this task in Excel 2007.

On a side note, I just joined and look forward to participating in this community. It seems like an excellent resource and hopefully one day (after i polish up my excel skills) I may be able to assist others as well. Thank you to all who make this great resource a possibility!

[6StringJazzer]

In B1, put

=COUNTIF(C:C,A1)

Copy down to row 500. Whenever the result is 0 that means that the item in column A was not found in column C. You can sort on it and filter it.

In D1 put

=COUNTIF(A:A,C1)

Copy down to row 300. Whenever the result is 0 that means that the item in column C was not found in column A.

[6StringJazzer]

On a second read-through, I see that you were looking for special formatting. You can use conditional formatting using the same logic, but you can't sort or filter on that.

[Wonton]

Wow, that was quick and it worked! Thank you so much for your assistance.

No worries on the special formatting. I will be sorting through them and your method makes this much more simple and accomplishes exactly what I need. Thanks again!

[Wonton]

Hm.. I am actually still having troubles with this problem. So essentially what I have is a list of 726 companies in A2:A727 whom were customers in 2010. I have another list of companies in C2:C678 which were customers in 2009. I'm trying isolate all the customers who used to be customers in 2009, that are no longer customers in 2010.

So would the correct function be COUNTIF(A:A, C2) and copy that down? And all the 0's would be what I am searching for? Thanks!

[6StringJazzer]

That's correct.