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)
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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. :)
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top