DonationCoder.com Software > Post New Requests Here

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

<< < (2/2)

rjbull:
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.

beethoven:
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.

4wd:
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.

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

Ask over in the Excel forum at VBAExpress:

http://www.vbaexpress.com/forum/

Target:
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)

Navigation

[0] Message Index

[*] Previous page