solver

[Roberto]

I have the following problem:
One worksheet (Payments) has three columns named "cheque#"; "Account#";
and "Amount".
Another worksheet (Invoices) has three columns named "Account#";
"Invoice#"; and "Amount".

On the worksheet Payments there is one cheque# for each account# for a
certain amount.
On the worksheet Invoices there many Invoice# for different amounts with
the same Account#.

I would like to identify those invoices/amount for a given Account# that
totalize the amount paid with cheque#.

Example:
Worksheet Payments
"cheque#" "Account#" "Amount"
20070101 ABC-001 300.75
20070102 BCD-001 605.35
......... ....... ......



Worksheet Invoices
"Account#" "Invoice#" "Amount"
ABC-001 1001 100.25
ABC-001 1002 110.50
ABC-001 1003 200.25
ABC-001 1004 50.25
ABC-001 1005 90.00
........ .... .......
BCD-001 500 101.50
BCD-001 501 60.25
BCD-001 502 205.60
BCD-001 503 25.60
BCD-001 504 55.55
BCD-001 505 258.35

On this example the cheque# 20070101 should appear linked to invoice#
1001; 1002 and 1005.
The cheque# 20070102 should appear linked to invoice# 501; 502; 503; 504
and 505.

Thank you for your help solving this puzzle.

Rob

 

[Ben McBen]

Hi Roberto

Before you even start thinking about how to solve this technically, I think
that you are in danger of not solving the use case. It looks to me as if
there could be many solutions to any given cheque, and there is no
deterministic way to link your data together. Unless you can guarantee that
there is only one possible solution to any cheque, you will be in trouble
later.

Yes - in your example there may be only one solution, but surely, in real
life, this will not be the case.

I think you need to change the way you record data to ensure a firm link can
be made.