26
Post New Requests Here / Comparison of the contents of 2 xls files to isolate missing records
« on: July 14, 2012, 03:43 AM »
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.
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.