Matching Values in Tables/Lists

[ctags009]

Hi all

Is there some kind of matching procedure that could be created to find matches in 2 tables. The catch is I'm not just looking for like values. For example:

List 1 has 30 records (Shows all of the deposits that were made to the bank in a given month -usually 1 per day)
List 2 has 100 records (which breaks down individual checks received)
Unfortunately, All i have are the values of the checks, no other information that can be used to match the checks with the deposits.

Some of these will be matching (there was only 1 check for that days deposit)
However, others will have multiple checks (usually not more than 5)

Is there any way to efficiently find which values in list 2 will add up to equal the values in list 1?

Any thoughts? THanks!

 

[spikepl]

No. Because the information is not there, according to your descripton of what is avalaible. If you have 30 deposits of 100 and 60 checks of 50 each there is no way to match specific checks to specific deposits.You can be lucky and each check and deposit is a unique amount, and so only one combination matches for the month, but I don't quite see the point of a system based on luck.

 

[ctags009]

No. Because the information is not there, according to your descripton of what is avalaible. If you have 30 deposits of 100 and 60 checks of 50 each there is no way to match specific checks to specific deposits.You can be lucky and each check and deposit is a unique amount, and so only one combination matches for the month, but I don't quite see the point of a system based on luck.

It's not the specific deposits I'm looking to match... it's just the like values. So in your example, list one value one would match up with list 2 values 1 and 2, list one value 2 would match up with list 2 values 3 and 4, and so on.... The reason this has to be done is because there will be leftover records in each set, which will tell me how much of the deposit is in transit (has not cleared the bank), but I need to know specifically which values to be able to record them properly. It's something I have to do manually each month and takes long enough to do where I'm willing to spend some time to try to automate it... I'm just not completely sure where to start

 

[spikepl]

If you are asking which amount on checks in total have been cleared and appear as deposits, and which total amount has not, then the difference in totals is the total amount in transit. And you wish to split it out on some random checks that may or may not have been cleared, as long as this difference fits the sum of those checks?

To be honest, with the info present so far, I'd ask my boss to reconsider the workflow rather than automating it

If you wish to record - "properly" - the amounts, how can you tell whether 2*100 is underway or 1*200?

 

[ctags009]

I know it seems like this would be bad practice to automate. BUT, in the year I have been doing it in this fashion I have only run into like amounts on very few occasions and they are always of very small denominations ($50 - $100) - which would be very easy to reconcile. Otherwise there is really only ONE way that the numbers could be added together to prove out. I would say 99% of the time this will work perfectly, the other 1% it would be pretty quick/easy for me to see where it went wrong.

My thoughts are to start with the 1st record in list 1. Store the key, then have it loop through list 2 to see if there are any exact matches. If so, it will assign that value the key of the 1st record. If not, it will store record 1 and go through the rest of the list to see if record 1 plus any of those records will equal the first record in list 1, if so, it will assign both values the key. I would like to be able to go through the list in a similar fashion until it can no longer find matches. Then i would simply run a query with my date parameter and see which values matched and which ones did not.

 

[ctags009]

To be honest, with the info present so far, I'd ask my boss to reconsider the workflow rather than automating it

Haha, everything gets checked before the data is actually used. This will just take the busy work (Manual matching ) out of the equation.

 

[ctags009]

bump- any ideas?