Number Matching, one to many

[Lonnie_Rudd]

Hello, I'm trying to find matches for a long column of numbers, varies
between 500 to 2,000 or so (these are checks), that match another column of
numbers that varies between 200 to 500 (these are deposits). Each deposit can
have one or lots of checks in it. For example a deposit of $100 may have
checks of $25, $35, $10.76, & $29.24. The end total is not the same, and it
should be when you take outstanding checks into consideration. What I need to
know is which checks tie to which deposits. The bank statements we get have
only the total deposit amount on them, not the detail, and while I could get
detail from the bank on this, it will take a couple of weeks (the bank is in
Scotland), and it would be nice to wrap this up faster than that since I'm
over-worked and under-paid. Can anybody help me with this? I would truly
appreciate it.

 

[Chip Pearson]

Any procedure that would do this would just be guessing at which checks
correspond to which deposits. For example, you might have five $20 checks.
Those five checks add up to $100, but are they really the checks that made
up the $100 deposit? Who knows? The $100 deposit may be made up of any zero
or more of those five $20 checks, along with any number of checks with
different values. Just because you find a combination of checks that sums to
the value of a deposit doesn't mean that it is those checks that actually
made up that deposit. It's all a guess.

The code to do any sort of matching would be just guessing at what
corresponds with what. Most likely, it wouldn't reflect reality. Probably
not worth the effort.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Lonnie_Rudd]

The $100 was just an example. A real life deposit would be something like $23,
678.46, which means that it would be less likely to have multiple hits. In
any case, what I'm trying to do is thin out what I have to look at. If I
could get code that would either give me a hit list or go to the next hit
(like the find function) then I would eliminate the ones with single hits,
which would be most, and go from there. It would be worth the effort.

Any procedure that would do this would just be guessing at which checks
correspond to which deposits. For example, you might have five $20 checks.
Those five checks add up to $100, but are they really the checks that made
up the $100 deposit? Who knows? The $100 deposit may be made up of any zero
or more of those five $20 checks, along with any number of checks with
different values. Just because you find a combination of checks that sums to
the value of a deposit doesn't mean that it is those checks that actually
made up that deposit. It's all a guess.

The code to do any sort of matching would be just guessing at what
corresponds with what. Most likely, it wouldn't reflect reality. Probably
not worth the effort.

Hello, I'm trying to find matches for a long column of numbers, varies
between 500 to 2,000 or so (these are checks), that match another column

[quoted text clipped - 15 lines]

over-worked and under-paid. Can anybody help me with this? I would truly
appreciate it.

 

[Lonnie_Rudd via OfficeKB.com]

Oh, and I can use this in other applications also. Surely it's possible. Can
anybody help me with this?

The $100 was just an example. A real life deposit would be something like $23,
678.46, which means that it would be less likely to have multiple hits. In
any case, what I'm trying to do is thin out what I have to look at. If I
could get code that would either give me a hit list or go to the next hit
(like the find function) then I would eliminate the ones with single hits,
which would be most, and go from there. It would be worth the effort.

Any procedure that would do this would just be guessing at which checks
correspond to which deposits. For example, you might have five $20 checks.

[quoted text clipped - 14 lines]