Search for the best match through amounts to achieve a certain total.

[Ronald]

Hello everyone,

My question is if you know a way to search through an array of amounts to try to match a certain value.

Example:

array:

123.45
32.65
567.43
2134.56
23.56
40.21
Amount I try to match: 56.21

Result: it shows that rows 2 and 5 match my amount (or within an mismatch range, e.g. the same result if my search amount was 56)

Do you maybe have a way to achieve this?

Regards,

Ronald.

 

[Luke M]

Hi Ronald,

Can you walk us through the logic of why rows and and 5 are the best? How many combinations are allowed (can we take 3 items, or just 2)?

 

[Ronald]

Sorry if I wasn't clear enough.
Row 2 and 5 match the amount I'm looking for.
Row 2: 32.65 + row 5: 23.56 = 56.21

The amount of combinations is not really an issue, if I at least have one (in more than 90% that will suit)

Background (why):
I get big amounts that customers pay for they orders (this includes many orders) and want to have the best match to this paid amount from the list of open invoices.
I know, they should specify order/invoice nr. etc. but that's indeed the ideal world

Hope this clarifies.

 

[Hui]

There are a few techniques you could try:
Use Solver:
http://www.k2e.com/tech-update/tips/147-using-excel-to-identify-entries-that-add-to-a-specific-value

Use a UDF:
http://www.tushar-mehta.com/excel/templates/match_values/

 

[Ronald]

Thanks Hui.

Pity enough Solver doesn't work properly in this case. It match very illogical amounts leaving too much difference (despite of various tweeks).
And option 2 is paid :-( (beside a trial version that works very well)

 

[Lori]

The attached workbook uses a single array formula to return the closest three matches among all combinations of numbers.

To extend the range size you can replace B8 with B10 and then select the new range to fill, click the formula bar and hold down CTRL+SHIFT+ENTER.

Also see here: http://forum.chandoo.org/threads/al...ual-to-a-given-sum-non-vba.16808/#post-133809

 

[Ronald]

Thanks Lori, looks promising.
I tried to extend the whole thing to have 100 rows and column 4 & 5 included applying your instructions but only got #VALUE! errors :-(

Can I maybe ask you to extend it for me so it works from row 3 (as now) to row 100 (i.e. of 8)?

Regards,

Ronald.

 

[XOR LX]

Unfortunately there's a theoretical limit on the size of the dataset with such a set-up of 20 entries. And even at 18/19 entries Excel may well run out of resources, in my experience.

With that sort of number, you would be best advised to seek a VBA-based solution.

Regards

 

[Lori]

Ronald, with the maximum 20 entries this took about 6 seconds to calculate the closest 10 matches - which is relatively quick considering it's calculating a matrix of 21 million values (this is helped by using a single array formula approach). But as XOR LX says you will run into difficulties extending beyond this as the number of possibilities to check grows exponentially.

Given 100 values to test, you will need to restrict the number of possibilities somehow. If you're willing to use a sum consisting of up to 4 of the entries that could be doable using a VBA program but it will likely become too large beyond that.