Welcome Guest.   Make a donation to an author on the site September 20, 2014, 03:00:54 AM  *

Please login or register.
Or did you miss your validation email?


Login with username and password (forgot your password?)
Why not become a lifetime supporting member of the site with a one-time donation of any amount? Your donation entitles you to a ton of additional benefits, including access to exclusive discounts and downloads, the ability to enter monthly free software drawings, and a single non-expiring license key for all of our programs.


You must sign up here before you can post and access some areas of the site. Registration is totally free and confidential.
 
The N.A.N.Y. Challenge 2013! Download dozens of custom programs!
   
   Forum Home   Thread Marks Chat! Downloads Search Login Register  
Pages: [1]   Go Down
  Reply  |  New Topic  |  Print  
Author Topic: Comparison of the contents of 2 xls files to isolate missing records  (Read 4822 times)
beethoven
Supporting Member
**
Posts: 38


View Profile Give some DonationCredits to this forum member
« on: July 14, 2012, 03:43:50 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.


* AOE13.jpg (490.59 KB, 1457x656 - viewed 186 times.)

* WOE13.jpg (165.73 KB, 713x633 - viewed 152 times.)
Logged
nudone
Cody's Creator
Columnist
***
Posts: 4,116



see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #1 on: July 14, 2012, 03:58:28 AM »

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.
Logged
4wd
Supporting Member
**
Posts: 3,332



see users location on a map View Profile Read user's biography. Give some DonationCredits to this forum member
« Reply #2 on: July 14, 2012, 07:00:14 AM »

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

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

I do not need to control my anger ... people just need to stop pissing me off!
nudone
Cody's Creator
Columnist
***
Posts: 4,116



see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« Reply #3 on: July 14, 2012, 08:04:48 AM »


That looks like an even better Excel feature than the one I messed about with. Will bookmark that.  thumbs up
Logged
beethoven
Supporting Member
**
Posts: 38


View Profile Give some DonationCredits to this forum member
« Reply #4 on: July 14, 2012, 08:29:33 AM »

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.
Logged
rjbull
Charter Member
***
Posts: 2,758

View Profile Give some DonationCredits to this forum member
« Reply #5 on: July 14, 2012, 03:30:27 PM »

I suspect "loose" comparison may prove hard to find.  In addition to the free programs above, there's also Florencesoft™ DiffEngineX™ for comparing Excel worksheets.  It's payware, but has been on Bits du Jour a couple of times.  It would be worth seeking out the Bits comments.
Logged
beethoven
Supporting Member
**
Posts: 38


View Profile Give some DonationCredits to this forum member
« Reply #6 on: July 14, 2012, 07:23:54 PM »

yeah, not being that tech savvy I probably underestimated the difficulty.  I thought this was more a long the lines of some formula like if value in column K is identical in sheet A & sheet B based on time stamp in column B and  column I is some code 9 (varying by up to 2 minutes) then format cell  in red -- obviously some cut an and paste is still necessary to position the relevant fields in a better way. Or if necessary a lot of information can be cut out leaving only the bare minimum of data fields.  The original idea or hope of course was to use the original files and to minimise prior handling.
Logged
4wd
Supporting Member
**
Posts: 3,332



see users location on a map View Profile Read user's biography. Give some DonationCredits to this forum member
« Reply #7 on: July 14, 2012, 08:53:28 PM »

The only easy way, (relative term), would be through VBA I think, (unless you want to export the sheets to another format, eg CSV, and use another program).

eg.
1) Grab amount entry from spreadsheet A, (X)
2) Look for a matching amount entry in spreadsheet B, (Y)
3) If X = Y then check to see if the time between them is less than 2 minutes
4) If it is, mark both entries as OK and go back to step 1.
5) If time doesn't match, go back to step 2.
6) If at the end of all values in spreadsheet B you haven't found a suitable match then flag the entry in spreadsheet A and go to step 1 until you've cycled through all entries in spreadsheet A.

The problem with that is if multiple entries occur within the space of a couple of minutes with the same amount.

It would probably make things a lot easier if you could synchronise the time between the two systems, eg. install a NTP server on one and have the other set its time from it.
Logged

I do not need to control my anger ... people just need to stop pissing me off!
AndyM
Charter Member
***
Posts: 616


see users location on a map View Profile Give some DonationCredits to this forum member
« Reply #8 on: July 15, 2012, 08:31:47 PM »

What 4wd said, you could do this with VBA.

Ask over in the Excel forum at VBAExpress:

http://www.vbaexpress.com/forum/
Logged
Target
Honorary Member
**
Posts: 1,404



see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #9 on: July 15, 2012, 09:08:57 PM »

just to confirm my understanding, the points of comparison are as follows

by timestamp (with a tolerance of +/- 2 minutes), then if you get a 'match' by value

or look for a transaction that is within a corresponding envelope, then see if the transaction value matches...

and do you want to see the result in a separate worksheet/book, or within the individual sheets?

And you should be able to attach some sample files if you archive (zip?) them first (that would make it easier to try and come up with an answer)

Logged

"Look wise, say nothing, and grunt. Speech was given to conceal thought" - Sir William Osler
Pages: [1]   Go Up
  Reply  |  New Topic  |  Print  
 
Jump to:  
   Forum Home   Thread Marks Chat! Downloads Search Login Register  

DonationCoder.com | About Us
DonationCoder.com Forum | Powered by SMF
[ Page time: 0.036s | Server load: 0.01 ]