I need to compare 2 csv files

ascott

New Member
Joined
Aug 28, 2005
Messages
2
I have 2 csv files of similar data although neither of the files will have all the data the other one does. Each file has a column of data that is the same (user names exported from active directory from one and from ldap from the other). I need to compare them and combine the all data from each file to a 3rd if there is a match.

Any help would be appreciated.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
I think you can use a vlookup to get to where you want to be.

Your Vlookup can be from file A to file B, or from file B to File A - it doesn't make a difference since you want all distinct rows.

Assuming you have performed your Vlookup command in File B against File A, you can then take all of the rows from Spreadsheet A (this will give you the rows that match both spreadsheets, as well as those that don't match from Spreadsheet A), and combine those in a third spreadsheet with the #N/A vlookups on Spreadsheet B, since these represent the rows of data that existed in Spreadsheet B, but not in A.

HTH, and let me know if you have further questions on this.
colbymack
 
Upvote 0
colbymack said:
I think you can use a vlookup to get to where you want to be.

Your Vlookup can be from file A to file B, or from file B to File A - it doesn't make a difference since you want all distinct rows.

Assuming you have performed your Vlookup command in File B against File A, you can then take all of the rows from Spreadsheet A (this will give you the rows that match both spreadsheets, as well as those that don't match from Spreadsheet A), and combine those in a third spreadsheet with the #N/A vlookups on Spreadsheet B, since these represent the rows of data that existed in Spreadsheet B, but not in A.

HTH, and let me know if you have further questions on this.
colbymack
I've been trying to use the vlookup command but I don't understand how it should work on two different files. I want to compare column a in both files One file has 1730 entries and the other has 3070 entries.
 
Upvote 0
Vlookup is a somewhat tricky command to understand initially, but can be used as a "powerful" tool (powerful is subjective, hence the quotes) that is similar to a database join, if you are familiar with that.

Anyway, here is an example, assuming you have Sheet 1 and Sheet 2 with data in Sheet 1, Column A that matches the Data in Sheet 2, Column A by data type. Let's further say that they are of format 'last name, first name'. Also, data begins in cell A2, since A1 is a title row. Sheet 1 and Sheet 2 can either be in the same workbook, or in separate workbooks.

in Sheet 1, cell B2, you can begin your vlookup by typeing in:
=vlookup(
Then click on cell A2. This tells Excel that you are looking for a value that matches A2.
That should now make the formula look like
=vlookup(A2
Now press the comma key - this tells excel that you are ready to tell it where to look for the matching contents. At this point, you need to click on Sheet2 and highlight column A by clicking on the A at the top of that column (in the gray area).
If you are doing 2 sheets in the same workbook, the formula will now look like: =vlookup(A2,Sheet2!A:A
press the comma key. This indicates that you are ready to tell Excel what value you want to return. The value you are about to enter is a column in a position relative to the column you just selected. In our case, we only highlighted one column, b/c we were only interested in that column's values. If you wanted to return the value of the cell in column B instead, you would have highlighted column A and B. In other words, you can look for a value in Column A, and return a value from column B (or any other column) as long as you highlight all of the columns. We are going to tell it column 1 (again, because we only highlighted one column, and we are just testing for existence of the value)
Anyway, our formula should now look like this:
=vlookup(A2,Sheet2!A:A,1
Press the comma key again. We are now ready to tell Excel to look for an exact match. Do this by typing in 'false' after the comma (minus the quostes) and close the formula with a right parentheses.
The final formula should look like this:
=VLOOKUP(A2,Sheet2!A:A,1,FALSE)

Copy this down in sheet 1 for as many cells as there are values in column A in Sheet 1. The result will display either the same value, or a #N/A. The #N/A's indicate that no matching value existed in Sheet 2. The cells with the same value indicate that the value exists in both sheets.

If you take all of the #N/A rows from sheet 1, and combine that with all of the rows in sheet 2, you will have a population of all rows with no duplicates (assuming there were no duplicates in Sheet 2 to begin with).

Hope this helps.

colby
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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