How to compare two excel files

brad-brad- Member Posts: 1,218
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.

Comments

  • msteinhilbermsteinhilber Member Posts: 1,480 ■■■■■■■■□□
    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.
  • eMeSeMeS Member Posts: 1,875 ■■■■■■■■■□
    brad- wrote: »
    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
  • jibbajabbajibbajabba Member Posts: 4,317 ■■■■■■■■□□
    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 :)
    My own knowledge base made public: http://open902.com :p
  • brad-brad- Member Posts: 1,218
    Gomjaba wrote: »
    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.
  • astorrsastorrs Member Posts: 3,139 ■■■■■■□□□□
    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-brad- Member Posts: 1,218
    astorrs wrote: »
    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
  • astorrsastorrs Member Posts: 3,139 ■■■■■■□□□□
    brad- wrote: »
    wow that looks awesome
    and it is. icon_thumright.gif

    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.
  • jibbajabbajibbajabba Member Posts: 4,317 ■■■■■■■■□□
    me wrote:
    No serious, we used to have a program in my last job to compare / search large documents (and I mean LARGE documents).
    astorrs wrote: »
    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 :D

    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 ..
    My own knowledge base made public: http://open902.com :p
Sign In or Register to comment.