MS Excel guru's in here please - Comparing lists of data

DoubleCheese · 10 May 2010 at 17:23

Afternoon all,

I have two lists of data, one Master file with 23000 (in column A) entries and my list with about 400 entries (in column C and a different workbook). The data in each cell is a mixture of letters and numbers so sorting alphabetically doesn't work.

I need to check that all my 400 entries are on the Master file and highlight any duplicates and those that aren't listed.

Can anyone devise a formula that will do this?

Thanks

Thrash · 10 May 2010 at 17:44

First step would be to use vlookup to compare the two lists.

Spuds · 10 May 2010 at 18:30

Assuming this is a one off check I'd go down the quick and easy route. To check for duplicates in the master list put the following in a spare column:

=COUNTIF(A:A,A1)

This will count how many times the entry in cell A1 appears in column A. Fill this down the length of the data (Excel should automatically change the A1 to A2, A3 and so on) and sort to find any duplicates which will have a value greater than 1.

To check that each entry in the second list is in the master list you can do something similar. Add the following in a spare column beside the second list:

=COUNTIF([Book1.xlsx]Sheet1!$A:$A,C1)

This assumes that both workbooks are in the same folder, rename Book1.xlsx to whatever the workbook containing the master list is called, same for the Sheet1. Again fill this down the length of the list you want to check and it will tell you how many times each entry appears in the master list. Sort/filter to find the ones which don't appear which will have a value of 0.

As I said quick and easy, if you need to do this on a regular basis you might want to look into a betterway of doing it.

Namsnik · 10 May 2010 at 21:43

If you don't actually need to identify the duplicates / missing entries but in fact just want to create a comprehensive list with no duplicates then I might copy the two data sets, one below the other, into a new worksheet. Then use Advanced filter to create a list of unique values. Finally copy this back to your master list. Just leave the criteria range blank.