Generate random numbers from a list that would equal to a given value

[Qutaibah]

Hi all,

Here is what I am trying to do, I have numbers from 1-13, I would like to be able to automatically generate numbers from that range in (5) columns like A, B, C, D, E and these numbers when added will equal a known value.

For example:

I have the numbers, 1,2,3,4,5,6,7,8,9,10,11,12,13
the target value is: 12

Excel would do this:

2 2 2 4 2 or 4 2 2 2 2 or 2 4 2 2 2 or even 5 1 4 1 1 what I am trying to do is getting every possible set of numbers that would equal the target value.

I don't know if this is even possible, but I am hoping... Thanks in advance!!

[Alf]

Perhaps this link may be of help?

http://www.tushar-mehta.com/excel/te.../match_values/

[Qutaibah]

Hi, thank you for your reply... I tried that (very similar to what I want) still this will generate any number equals to 12 even if it is a combination of 6 or 7 numbers where I am only looking for combination of 5 numbers. Also, the code in the provided link won't repeat. I don't care how many times a number is selected as long as when added to the other 4 numbers it would equal the target number. These numbers must be selected from 1-13. (if the target value was 10, then we would get 1,1,1,1,6 or 5,1,2,1,1 any combination that would equal 10) in this case 10 is just an example, if I enter 25, I want to be able to get 8,2,1,1,13 as one of the many possible combination. (I hope that I made that clear). Thanks for your help

[Alf]

Still not sure that I understand your problem properly.

I've added a file with Tushar-metha's macro. Do have a look at it again. As far as your request goes that you do want the values spread out in columns it could be fixed with a macro after running the Tushar-metha's macro.

Perhaps "Excel Solver" also would be of use but it would only find one solution to this kind of problem.

Alf

[Qutaibah]

Ok.. So I did some more research and found out that this code by Myrna Larson does the trick but still need some tweaking...

Please Login or Register  to view this content.

Now this code will successfully list all combination or permutation... What do I need to do to get the sum of each combination, since the numbers are divided with a separator I used some template (digit split) but this means I will have to do it manually for each entry. Also after I get the sum of each combo I would like to be able to filter the results based on the known sum, if I enter 15 as the desired sum, I want to be able to generate only these combos that equal to 15 when added up. Thank you for your time

[Alf]

Glad you solved your problem and thanks for sharing the "Myrna Larson" code. Will add it to my collection of useful macros.

Alf

[martindwilson]

you could just add a few lines of code to split the end result to seperate columns

[gdavos]

Hello all! I am new to this thread. I am having a similar problem running the following scenario:

I am trying to generate random numbers between 0 and 6 (*RANDBETWEEN(0,6))in four columns that equal 6. So for example,


k1 k2 k3 k4 Sum
1 2 3 0 6
0 1 4 1 6


So on so forth. I wanted to inquire whether the Myrna Larson method did work or can you use the Random Number Generation in the Data Analysis tab helped w/ the problem. If you could let me know, I would appreciate it.


Thanks,
Ganesh

P.S. I actually have M.S. Excel 2010

[protonLeah]

gdavos,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.