How to compare two excel files

brad- · 06-30-2009, 06:46 PM

I have two excel files with three columns each.

Address | city | zip.

One file I get from our database, one file I get externally. I need to compare the two files and exclude duplicates. So, if Address in file 1 = address in file 2, exclude it in the new sheet - edited sheet, whatever.

I dont know if it can be done in excel or not, i was hoping it could - since I'd like to give this routine to the manager that needs it and just let him do it all after I give him the files. I figure worst case scenario is that I would have to create a SQL db, import them both into seperate tables, and compare/export the table.

msteinhilber · 06-30-2009, 07:49 PM

While I forget the exact syntax, you might want to Google the INDEX and MATCH functions, they should be able to do what you want.

eMeS · 06-30-2009, 07:52 PM

Quote:

Originally Posted by brad-

I have two excel files with three columns each.

Address | city | zip.

One file I get from our database, one file I get externally. I need to compare the two files and exclude duplicates. So, if Address in file 1 = address in file 2, exclude it in the new sheet - edited sheet, whatever.

I dont know if it can be done in excel or not, i was hoping it could - since I'd like to give this routine to the manager that needs it and just let him do it all after I give him the files. I figure worst case scenario is that I would have to create a SQL db, import them both into seperate tables, and compare/export the table.

You could probably use the SEARCH function in Excel to do something like this (it might take some work to make it happen)....I think both files would have to be in separate worksheets in the same workbook. Out of the box I don't believe that Excel will perform a comparison of two different .xls files.

There are various shareware and freeware tools that will do exactly what you're asking. The proposed solution you've given sounds valid too....

MS

Gomjaba · 06-30-2009, 09:16 PM

30 Inch screen and side by side

No serious, we used to have a program in my last job to compare / search large documents (and I mean LARGE documents).

I'll dig a bit and report back

brad- · 06-30-2009, 09:38 PM

Quote:

Originally Posted by Gomjaba

30 Inch screen and side by side

No serious, we used to have a program in my last job to compare / search large documents (and I mean LARGE documents).

I'll dig a bit and report back

Thanks I appreciate it. I figured out a SQL solution, but if I cant get it to work in excel only that means I'll have to do this every time this supervisor wants.

captobvious · 06-30-2009, 10:23 PM

How did you get your job? JK!

Compare Spreadsheets for Excel

astorrs · 06-30-2009, 11:38 PM

Excel Add-ins, Advanced Excel Tips - DigDB a subscription will pay for itself in an hour if you find yourself mining Excel for data. Give the trial a whirl and I bet you'll be hooked.

brad- · 07-01-2009, 02:09 AM

Quote:

Originally Posted by astorrs

Excel Add-ins, Advanced Excel Tips - DigDB a subscription will pay for itself in an hour if you find yourself mining Excel for data. Give the trial a whirl and I bet you'll be hooked.

wow that looks awesome

astorrs · 07-01-2009, 02:47 AM

Quote:

Originally Posted by brad-

wow that looks awesome

and it is.

I've been using it for 5 years and couldn't live without it. I find myself using Excel for things I never though of using it before since it's so damn fast to clean-up data (from a DB, a report, or whatever).

The other Excel add-in I absolutely love is ASAP Utilities, the two of them are fantastic and complementary.

Gomjaba · 07:28 AM

Quote:

Originally Posted by me

No serious, we used to have a program in my last job to compare / search large documents (and I mean LARGE documents).

Quote:

Originally Posted by astorrs

Excel Add-ins, Advanced Excel Tips - DigDB a subscription will pay for itself in an hour if you find yourself mining Excel for data. Give the trial a whirl and I bet you'll be hooked.

Astorrs beat me to it .. That is the one we used. The guy was also very helpful when we had an issue with licenses etc., within the hour we received one which didn't require one (and no I don't share it lol). He also modified it so we could do some custom stuff

This is very powerful and worth every penny ... Our VIP department (I used to work for an online casino) was able to compare thousands of pages in a heartbeat ..