Can Excel figure a combination?

[Kalas]

I'm a CPA and frequently I run into a case where a client has combined numbers out of a series to arrive at a total. For instance, lets say he sent a list of 100 checks and he's combined these 100 checks into 5 different types of expenses. Is there a way I can put in the total for one expense category and have Excel make a suggestion as to what numbers from the list account for the total? I attempted to do this using the Solver add-in, but I don't think this is viable. It's a complex computation, I am sure, so it may outstrip Excel's capabilities.

Thank you,
Troy

 

[RalphA]

The number of combinarions of 1000 items, taken 5 at a time, is, per Excel's formula:
=COMBIN(1000,50) gives 8.25029E+12, or 8,250,290,000,000
As you can see, on average, you would have to try 1/2 of the above number, to get a result, and, I believe you would have to wait too long to accept this as a practical result. It would make more sense to try to get the actual information from your client. In my opinion, this would be a reasonable request.

Another problem might be that, if two or more of the 1000 items are not unique, you might still have more than one solution to choose from!

 

[RalphA]

Well, I entered the word "combinations" in the MeExcel's "Search", and found this:

http://www.mrexcel.com/board2/viewtopic.php?t=237280&highlight=combinations

Please let us know if this works for you. If not, you can persue the problem with those who gave an answer.

 

[barry houdini]

This looks similar to a past MrExcel "Challenge of the Month", see here

http://www.mrexcel.com/pc09.shtml

 

[tusharm]

To the extent that it can be solved with Solver "optimized" to expect a linear model, see the template
Find a set of amounts that match a target value
http://www.tushar-mehta.com/excel/tips/template-set-match.html

I'm a CPA and frequently I run into a case where a client has combined numbers out of a series to arrive at a total. For instance, lets say he sent a list of 100 checks and he's combined these 100 checks into 5 different types of expenses. Is there a way I can put in the total for one expense category and have Excel make a suggestion as to what numbers from the list account for the total? I attempted to do this using the Solver add-in, but I don't think this is viable. It's a complex computation, I am sure, so it may outstrip Excel's capabilities.

Thank you,
Troy

 

[RalphA]

Barry:

The site you posted seems perfect for what the original poster needs! Hope he goes there.



Edited 7:59 Central Daylight Time:
The very first time I tried to run the code, I got a "Compile error: Variable not defined", pointing to the variable CHECK. Arrgh! Am I supposed to try to fix this, or has it been fixed already?