Comparing two excel (.CSV) documents

big_andy

New Member
Joined
May 19, 2004
Messages
3
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.
(y)
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

AgentSmith

Well-known Member
Joined
Mar 8, 2004
Messages
575
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. :)
 

big_andy

New Member
Joined
May 19, 2004
Messages
3
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.
 

AgentSmith

Well-known Member
Joined
Mar 8, 2004
Messages
575
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!
 

elazab

New Member
Joined
Jun 23, 2014
Messages
1
You can try wwww.csvcompare.com

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.
(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,096,022
Messages
5,447,920
Members
405,472
Latest member
serhito

This Week's Hot Topics

Top