DonationCoder.com Software > Post New Requests Here

Comparison of the contents of 2 xls files to isolate missing records

(1/2) > >>

beethoven:
First, not sure if my request is suitable for here - please tell me if this is not of any general interest, too boring or too complicated.

My little feature, program or coding snack request is as follows:

I have two small xls files that contain output from different sources. While the underlying transactions are the same, I can't easily compare the files to see if any entry is only appearing in one file. I am looking for a simple procedure to compare the data in file 1 with the data in file 2 and highlight or flag or create an output file of any record that is not in both files.
Having studied the files, the only common information in both files is 1) an amount and 2) a time stamp.  Problem is that the amounts are not unique and the timestamps between file 1 and file 2 are not identical but vary by up to 1-2 minutes. So any comparison would have to be based on a) amount being identical and timestamp being within 2 minutes for the record in question.
I attach two files as samples ( some columns have been cleared or modified for privacy reasons) but these columns should be ignored anyway as they do not appear in both files. In my files two transactions are appearing only in the AOE13 File namely Order ID 40101 and 40103 - these are the files I would like to see flagged or similar. Sorry but since I can't upload xls files, I chose jpg for illustration.

nudone:
I've used Beyond Compare 3 to compare spreadsheets before. Available here (not sure what the free/trial version does): http://www.scootersoftware.com/

I appreciate this wouldn't be a solution without the spreadsheets being modified first, as I think Beyond Compare would still need the layout of the columns to be relatively similar.

Having said that. I've also used Excel's own features to compare two columns and colour the discrepancies. I can't remember what you have to do though. It was a method I found in Google so it must be a common problem/solution.

Again, not a one click solution, you'd have to copy and paste the columns around that you need to compare.

4wd:
Perhaps this page may help: How to Compare Two Excel Spreadsheets & Highlight What Is Missing

Otherwise, WinMerge, (free), has a prediffer Excel plugin.

nudone:
Perhaps this page may help: How to Compare Two Excel Spreadsheets & Highlight What Is Missing
-4wd (July 14, 2012, 07:00 AM)
--- End quote ---

That looks like an even better Excel feature than the one I messed about with. Will bookmark that.  :up:

beethoven:
Guys, thanks for the links and advice - I will study this more closely tomorrow.  I am not sure though if these programs will help me as it seems they pick up on minor and exact differences between files. My files are substantially different even though they relate to the same records. Only two data points can be directly compared and as I mentioned, for the time factor to be considered matching, the time has a variance of 1-2 minutes.

Navigation

[0] Message Index

[#] Next page