Finding amounts equal to one total

I hope I'm able to explain this properly. I'm trying to match lists of up to
a thousand checks with lists of maybe a hundred or more deposits. The number
of checks per deposit can range between one and dozen or more. I've been able
to make a spreadsheet using formulas to find if there are only two checks for
each deposit with this formula: =IF(($A$1+A2) = B1, "Match", False) then
dragging it down and changing it as necessary going across. The list of
checks is in column A with one deposit at a time in B1. Obviously it requires
most of the worksheet. My question has two parts; I have to use find to find
the matches, then look at the formula to see what cells it refers to and go
from there. Is there a way to write code to highlight the matching cells?
Maybe by bringing the cell references up in a dialogue box? Something like
"match found in cells A34 & A78."

Also, doing what I did above looking for 3 or more checks is HUGE. Is there a
way using VBA to find these? I've been trying to learn VBA with mixed results,
and I appreciate any help.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

Is there anything else to identify the entries-such as a received date on the cheques and
a deposit date on the deposits?
If, for example, the cheques had a received date you could group them by date and compare
the total to the deposit for that date or, if the deposits are made on infrequent dates,
accumulate the cheques within a deposit date range.

Failing having a date for a point of reference, i.e. having amounts only, it would be a
difficult task given that any number of cheque combinations could equal a deposit total..

--
Regards;
Rob
------------------------------------------------------------------------
"Frank R via OfficeKB.com" <u11209@uwe> wrote in message news:589ba7f2a3c17@uwe...
> I hope I'm able to explain this properly. I'm trying to match lists of up to
> a thousand checks with lists of maybe a hundred or more deposits. The number
> of checks per deposit can range between one and dozen or more. I've been able
> to make a spreadsheet using formulas to find if there are only two checks for
> each deposit with this formula: =IF(($A$1+A2) = B1, "Match", False) then
> dragging it down and changing it as necessary going across. The list of
> checks is in column A with one deposit at a time in B1. Obviously it requires
> most of the worksheet. My question has two parts; I have to use find to find
> the matches, then look at the formula to see what cells it refers to and go
> from there. Is there a way to write code to highlight the matching cells?
> Maybe by bringing the cell references up in a dialogue box? Something like
> "match found in cells A34 & A78."
>
> Also, doing what I did above looking for 3 or more checks is HUGE. Is there a
> way using VBA to find these? I've been trying to learn VBA with mixed results,
> and I appreciate any help.
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200512/1

All I have are amounts. I can pull the paperwork to look at each transaction,
and will if I have to, but there are hundreds of them, and maybe only one
that I need in a big box I'd have to look through. They're in storage and I
have to order the boxes in then look through them for the transaction. I'll
wind up with dozens of these to look through without having some kind of idea
where to start.

RWN wrote:
>Is there anything else to identify the entries-such as a received date on the cheques and
>a deposit date on the deposits?
>If, for example, the cheques had a received date you could group them by date and compare
>the total to the deposit for that date or, if the deposits are made on infrequent dates,
>accumulate the cheques within a deposit date range.
>
>Failing having a date for a point of reference, i.e. having amounts only, it would be a
>difficult task given that any number of cheque combinations could equal a deposit total..
>
>> I hope I'm able to explain this properly. I'm trying to match lists of up to
>> a thousand checks with lists of maybe a hundred or more deposits. The number
>[quoted text clipped - 12 lines]
>> way using VBA to find these? I've been trying to learn VBA with mixed results,
>> and I appreciate any help.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200512/1

Ouch!
If all you have are the amounts then it sounds like a massive task.
It would mean that for each deposit you'd have to go through all the cheque values to
determine what amounts add up to the deposit-and there could be an "infinite" number of
combinations.
Offhand I can't see any way to do it that would be simple given that, as you noted, there
are over a hundred deposits consisting of about a thousand cheques.
It's difficult to suggest anything without knowing what the object is. If you, for
example, are trying to reconcile receipts to deposits then I'd start with looking for
values that make up the reconciling balance (total deposits - total cheques).
This would cut down on your population - you'd only be interested in entries less than or
equal to the difference.

Again, without knowing what you are looking for it's hard to offer any advice.

--
Regards;
Rob
------------------------------------------------------------------------
"Frank R via OfficeKB.com" <u11209@uwe> wrote in message news:58a500fa2846c@uwe...
> All I have are amounts. I can pull the paperwork to look at each transaction,
> and will if I have to, but there are hundreds of them, and maybe only one
> that I need in a big box I'd have to look through. They're in storage and I
> have to order the boxes in then look through them for the transaction. I'll
> wind up with dozens of these to look through without having some kind of idea
> where to start.
>
> RWN wrote:
> >Is there anything else to identify the entries-such as a received date on the cheques
and
> >a deposit date on the deposits?
> >If, for example, the cheques had a received date you could group them by date and
compare
> >the total to the deposit for that date or, if the deposits are made on infrequent
dates,
> >accumulate the cheques within a deposit date range.
> >
> >Failing having a date for a point of reference, i.e. having amounts only, it would be a
> >difficult task given that any number of cheque combinations could equal a deposit
total..
> >
> >> I hope I'm able to explain this properly. I'm trying to match lists of up to
> >> a thousand checks with lists of maybe a hundred or more deposits. The number
> >[quoted text clipped - 12 lines]
> >> way using VBA to find these? I've been trying to learn VBA with mixed results,
> >> and I appreciate any help.
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200512/1

If this is a "real life" example....
Contact your bank and ask for a text file with as much detail as they can
provide for the deposits in question. If your company has a good relationship
with the bank (read many millions held there), you may get it for free.
Otherwise, the fee will be nominal compared to the hours spent finding
alternatives.

Once you get that file, you can parse it and get the details you need.

That's a quasi-Excel approach, but that's what I would do.

***********
Regards,
Ron


"Frank R via OfficeKB.com" wrote:

> I hope I'm able to explain this properly. I'm trying to match lists of up to
> a thousand checks with lists of maybe a hundred or more deposits. The number
> of checks per deposit can range between one and dozen or more. I've been able
> to make a spreadsheet using formulas to find if there are only two checks for
> each deposit with this formula: =IF(($A$1+A2) = B1, "Match", False) then
> dragging it down and changing it as necessary going across. The list of
> checks is in column A with one deposit at a time in B1. Obviously it requires
> most of the worksheet. My question has two parts; I have to use find to find
> the matches, then look at the formula to see what cells it refers to and go
> from there. Is there a way to write code to highlight the matching cells?
> Maybe by bringing the cell references up in a dialogue box? Something like
> "match found in cells A34 & A78."
>
> Also, doing what I did above looking for 3 or more checks is HUGE. Is there a
> way using VBA to find these? I've been trying to learn VBA with mixed results,
> and I appreciate any help.
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200512/1
>

Unfortunately it isn't that simple. Some of these go back to the late '90's.
I work for an accounting firm and this is part of an audit that we are
getting no help on. All I have are amounts and I can pull records but I have
to ask for the specific record. I can do it with simple formulas in Excel,
but it will take a very, very long time. Thanks anyway.

Ron Coderre wrote:
>If this is a "real life" example....
>Contact your bank and ask for a text file with as much detail as they can
>provide for the deposits in question. If your company has a good relationship
>with the bank (read many millions held there), you may get it for free.
>Otherwise, the fee will be nominal compared to the hours spent finding
>alternatives.
>
>Once you get that file, you can parse it and get the details you need.
>
>That's a quasi-Excel approach, but that's what I would do.
>
>***********
>Regards,
>Ron
>
>> I hope I'm able to explain this properly. I'm trying to match lists of up to
>> a thousand checks with lists of maybe a hundred or more deposits. The number
>[quoted text clipped - 12 lines]
>> way using VBA to find these? I've been trying to learn VBA with mixed results,
>> and I appreciate any help.

--
Message posted via http://www.officekb.com