Comparing 2 spreadsheets

[fuzzyjon]

Hello everyone,

I wonder if anyone could please provide me with any assistance on the following problem that I have.

OK, I have two separate spreadsheets.

The first contains a list of ALL our clients who have done a particular project, with the Company Name, Company Address and Company Town fields.

The second contains a list of SOME of our clients who have done a different project, with the same fields as above.

I would like to compare the two spreadsheets and make it so that the ALL list has the records from the SOME list removed, so I am left with the rest of our clients who have not done the different project.

Phew!! Hope this makes sense!!

Thanks for your time and help.

Best wishes

Jon

 

[Richard Schollar]

Hi Jon

Probably easiest if on the All sheet you use a helper column formula (ie next adjacent empty column) along the lines of:

=ISNUMBER(MATCH(All_Sheet!A2,Some_sheet!$A$2:$A$1000,0))

which will return True if the company name (assuming it is in the A column on both sheets) from the All sheet exists on the Some sheet, and False if it doesn't. Amend the ranges as appropriate.

Then, on the All sheet, use Autofilter (Data>Filter>Autofilter) and filter this helper column for the False values. This will give you only company names which do not appear on the Some sheet.

Best regards

Richard

 

[fuzzyjon]

Thanks!

Hi Richard

Thanks for the quick reply..... it worked perfectly too!!!

Cheers and have a great weekend.

Jon