Find a specific amount outstanding?

[js65572]

Hi all,

I am looking to find a specific amount difference (36446.88) based on multiple amounts matching.

I have 71 different scenarios of amounts that could add up to the specific amount mentioned above.

Is there a way to get Excel to look at every possibility of matching specific figures to find the above?

Thank you for any help in advance

 

[Special-K99]

I believe Solver should be able to do this but I have no other knowledge of it.

Search the forum or do a google for solver.

 

[js65572]

I believe Solver should be able to do this but I have no other knowledge of it.

Search the forum or do a google for solver.

Thank you, I had read the Solver could assist although it doesn't seem to be active on my version of Excel - Even when trying to add via Add-ons

 

[sheetspread]

Which Excel do you have? In 2010 you must select from the Data ribbon, 2003 is tools.

One way to find the components of a total:

https://www.mrexcel.com/forum/excel-questions/903401-highlight-show-cells-if-sum-matches-x.html

 

[Eric W]

First, to activate the Solver, click File > Options > Add-ins > on the bottom: Manage: Excel Add-ins > Go > check the Solver Add-in box > OK. The Solver should now be available on the right side of the Data tab. This is for Excel 2010 or newer. Look at sheetspread's link for how to use it.

Second, that still may not work. Or it may return a vastly different set of numbers than you want. It's quite surprising somehow that there may be many solutions. One further option is to check this link:

https://www.mrexcel.com/challenges/accounts-receivable-challenge/

Many years ago there was a challenge here to solve exactly that kind of problem, and someone came up with a very nice macro. I've tried it and it works. I had to tweak it a bit, but nothing major. I don't have time to adapt it for you, but if you have some VBA experience, it should work nicely. Or someone else here may help. Good luck!