Rod, Few facts about the code...
It (subset sum) is a well-known, well-discussed problem in the field of computer science. It is a vast subject. For relatively smaller lists, for example, the one in your original post, the algorithm hardly takes less than a minute to find the subsets. For lists containing more than 20 elements it takes more than 2 minutes. I my-self tried it with a list of 54 numbers twice, and in the end, had to End Task excel. It was running for approx. 30 minutes. Below is one more link that gives a solution to the subset sum problem... http://www.mrexcel.com/pc09.shtml Regards, Ajit -----Original Message----- From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Rod Urand Sent: Tuesday, May 12, 2009 8:44 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Re: A tricky scenario that may need a function or VBA. Hi there, Sorry it took too long to return your reply. I really appreciate your prompt reply. This was exactly what the person in the class needed. I really cannot thank you enough. Kindest Regards, Rod Urand. On May 8, 8:32 am, "Ajit Navre" <ajit.na...@gmail.com> wrote: > Hi, > > Find attached the file with the solution. The solution is based on the > Subset Sum Algorithm. Check the following link... > > http://www.clausbrod.de/cgi-bin/view.pl/OneSpaceModeling/MacroSubsetSum > > I have made certain enhancements (hopefully) in following areas--- > 1. The list need not be in a sorted order. > 2. Provided a class-based interface. > > Sheet1 contains the test data Column A. > > The Class modules contain supporting classes. classSubsetSum is the core. > > Module1 has an UDF. Can be used from worksheet. As in sheet1-ColumnB. > ThisWorkbook has a sample() macro that can be run from Tools->Macro. The out > put is dumped in the debug window. > > Let me know if this is helpful. > > Also share any improvements/suggestions with me... > > Regards > > Ajit > > > > -----Original Message----- > From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] > > On Behalf Of Rod Urand > Sent: Friday, May 08, 2009 3:05 AM > To: MS EXCEL AND VBA MACROS > Subject: $$Excel-Macros$$ A tricky scenario that may need a function or VBA. > > I am a self employed Excel trainer that teaches basic, intermediate > and advanced Excel to various groups. I can handle most questions, but > I was stumped in the class today. > > Here is the scenario. > > Given a set of numbers in a range, and a separate cell with an > individual number in it, is there a way to look at that individual > cell and determine how many possible combinations of numbers there > could have been from the original set of numbers in a range that made > up the answer equal to that number. I dont know how to attach a > screenshot, but I have an example i can send... > > Here is what was asked... > > 100 > 200 > 300 > 50 > 80 > 20 > 10 > > Above is the range of numbers > > here is the answer: 160 > > From the original list, what possible combinations of numbers could be > added to get to that answer. (Just looking at the list you can see > that 100+50+10 is one combination as well as 80+20+10 as another.) > The person just wants to know the cell locations of all the cells that > could have gone into make this number. > > The result in this case would be 100,80,50,10 as all the possible > number combinations. > > Any help? > > > > SubSetSum.xls > 103KViewDownload- Hide quoted text - > > - Show quoted text - --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
