Results 1 to 6 of 6

Thread: Comparing two excel (.CSV) documents
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2004
    Location
    Detroit, MI
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Comparing two excel (.CSV) documents

    I've done a few searches and couldn't really find what i need.
    I'm not familiar with VB, but if given code, i could probably copy and paste and debug from there.

    I've got two .CSV files that i want to compare, and make a third .CSV file.

    File A, contains a list of information. column B will contain a name, and column C will contain another name, which is part of the name in Column B.

    File B will contain a list of similar names, with the same format and everything.

    What i'm looking for is a formula or macro that will create a third document that contains all of the information that is in File B, and add the rows of information that are in File A, but not in File B. This should be done by comparing the name in Column B in File A to Column B in File B. If a name in Column B from File A is not in File B, then the entire row should be copied to File C, with the name in Column B change to "Removed".


    I now it's possible to make something like this, and i can explain the logic a little better if needed, but a nudge in the right direction would be very helpful.

  2. #2
    New Member
    Join Date
    May 2004
    Location
    Detroit, MI
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing two excel (.CSV) documents

    Please Help!!

  3. #3
    Board Regular AgentSmith's Avatar
    Join Date
    Mar 2004
    Location
    Birmingham UK
    Posts
    575
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    So many options! Perhaps the easiest to explain is the one that involves some manual intervention:

    - Open both CSV files (I'll call them file1.csv and file2.csv)
    - In cell D1 of file1.csv enter the formula =COUNTIF([file2.csv]file2!B:B,B1) and copy this down.
    - Put a similar formula in cell D1 of file2.csv, referring to file1 instead.
    - Now you can see how many names match up in each file, so you can easily sort and/or autofilter on that
    - A bit of copying and pasting into a new spreadsheet and it's job done!

    Not exactly glamorous, but it should work.
    Mike
    Working with Vista and Office 2007.
    Check out my (relocated!) blog =)

  4. #4
    New Member
    Join Date
    May 2004
    Location
    Detroit, MI
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing two excel (.CSV) documents

    That worked great. now I just need to automate it

    If someone can give me a clue as to how i would go about doing this, that would great.

    I used the COUNTIF function to determine which rows weren't in the new file, and made the output of the function to be the word "Removed"

    then i had to copy and paste these rows that came up removed into the "new" document, and replace the text in column C with Removed.

  5. #5
    Board Regular AgentSmith's Avatar
    Join Date
    Mar 2004
    Location
    Birmingham UK
    Posts
    575
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It can be done entirely in VBA, but it is a bit of a faff to program. I've done it before, but I haven't got a clue where I saved it! It did take a while to get it working exactly right, so if I dig anything up I'll certainly post it.

    Anyway - at least you're up and running!
    Mike
    Working with Vista and Office 2007.
    Check out my (relocated!) blog =)

  6. #6
    New Member
    Join Date
    Jun 2014
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing two excel (.CSV) documents

    You can try wwww.csvcompare.com

    Quote Originally Posted by big_andy View Post
    I've done a few searches and couldn't really find what i need.
    I'm not familiar with VB, but if given code, i could probably copy and paste and debug from there.

    I've got two .CSV files that i want to compare, and make a third .CSV file.

    File A, contains a list of information. column B will contain a name, and column C will contain another name, which is part of the name in Column B.

    File B will contain a list of similar names, with the same format and everything.

    What i'm looking for is a formula or macro that will create a third document that contains all of the information that is in File B, and add the rows of information that are in File A, but not in File B. This should be done by comparing the name in Column B in File A to Column B in File B. If a name in Column B from File A is not in File B, then the entire row should be copied to File C, with the name in Column B change to "Removed".


    I now it's possible to make something like this, and i can explain the logic a little better if needed, but a nudge in the right direction would be very helpful.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •