Comparing lists in Excel

[zag]

Does anyone know what function can be used in Excel to compare two lists of items and list out items which appear in one list and not the other ?

What I mean is that I have two lists - master and secondary. The master list contains (say) 10,000 entries and the secondary list contains (say) 7,000. I want to return the 3,000 entries which aren't in the secondary list. The values would be unique single values such as pc00001, pc00002, pc00004, etc with no other associated values or keys.

I'm sure this type of thing must come up realtively often, but I can't work out an easy way to do this ?

z

 

[davidoco]

Place your lists in the same workbook but on different sheets

Sort both lists out in alphabetical order.

Say Sheet 1 has the full list and Sheet 2 has the list with matching valves

Name the range in Sheet 2 as “data” – just highlight the whole sheet and name if you wish

In Sheet 1 see A4 for your formula which you will need to copy down the list.


A1 __ A2 __ A3___ A4

PC001 ???? ???? =VLOOKUP(A1,data,1,FALSE)
PC002 ???? ???? copy down
PC493 ???? ???? copy down
PC490 ???? ????


If there is a match for PC001 in sheet 2 it will repeat the match or return n/a. You can then see your matching values and use Autofilter to filter out the n/a.

see here for example
[broken link removed]