Thank you Dana,

The solver solution worked for me. After I find a combination of numbers
that equal 120, I then may not use those specific numbers again. I move them
off the list and then rerun the solver on the remaining numbers, some of
which may be duplicates of the ones used in the first solution.

I appreciate your responses ... THANK YOU!
--
Cheers,
Cheryl


"Dana DeLouis" wrote:

If the function can not find a combination equal to ...120

Hi. I see you have duplicate data in your set.


Size 2

{22, 98}, {51, 69}, {57, 63}

Size 3

{18, 39, 63}, {20, 31, 69}, {20, 49, 51}, {21, 30, 69}, {21, 36,
63}, {22, 29, 69}, {26, 31, 63}, {26, 36, 58}, {29, 34, 57},
{30, 39, 51}, {31, 38, 51}

Size 4

{18, 18, 21, 63}, {18, 18, 26, 58}, {18, 20, 31, 51}, {18, 21, 30,
51}, {18, 22, 22, 58}, {18, 22, 29, 51}, {18, 22, 31, 49}, {18, 29,
34, 39}, {18, 30, 34, 38}, {20, 20, 22, 58}, {20, 20, 29, 51}, {20,
20, 31, 49}, {20, 21, 22, 57}, {20, 21, 30, 49}, {20, 22, 29,
49}, {20, 26, 36, 38}, {20, 30, 31, 39}, {20, 30, 34, 36}, {21, 22,
26, 51}, {21, 22, 38, 39}, {21, 26, 34, 39}, {21, 29, 31, 39}, {21,
29, 34, 36}, {21, 30, 31, 38}, {21, 31, 34, 34}, {22, 26, 34,
38}, {22, 29, 30, 39}, {22, 29, 31, 38}, {22, 30, 34, 34}, {26, 26,
29, 39}, {26, 26, 30, 38}, {26, 26, 34, 34}, {26, 29, 31, 34}

Size 5

{18, 18, 18, 30, 36}, {18, 18, 20, 26, 38}, {18, 18, 20, 30,
34}, {18, 18, 21, 29, 34}, {18, 18, 22, 26, 36}, {18, 20, 20, 26,
36}, {18, 20, 21, 22, 39}, {18, 20, 21, 30, 31}, {18, 20, 22, 22,
38}, {18, 20, 22, 26, 34}, {18, 20, 22, 29, 31}, {18, 20, 26, 26,
30}, {18, 21, 22, 29, 30}, {18, 21, 26, 26, 29}, {18, 22, 22, 22,
36}, {20, 20, 21, 29, 30}, {20, 20, 22, 22, 36}, {20, 21, 22, 26,
31}, {20, 22, 22, 22, 34}, {20, 22, 22, 26, 30}, {20, 22, 26, 26,
26}, {21, 22, 22, 26, 29}

Size 6

{18, 18, 18, 20, 20, 26}, {18, 18, 18, 22, 22, 22}, {18, 18, 20, 20,
22, 22}



Might be easier to sort your data for display first...

{18, 18, 18, 20, 20, 21, 22, 22, 22, 22, 26, 26, 26, 29, 30, 31, 34,
34, 36, 38, 39, 49, 51, 57, 58, 63, 69, 98}
= = = = =

HTH :)
Dana DeLouis









Cheryl Elizabeth wrote:
Hi there,

I regularly need to search through a lists of numbers to find a combination
that will equal a specified number ... is there a function that can help me?

For example, in the following list of numbers, I need to find the numbers,
or cells that equal 120.

31
18
18
26
26
18
26
34
34


21
69


30

98
36
51
22
29
49
63
38
39
20
22
22
20
22
57
58

One solution is: (this may not be the first solution a function would
return, just the first one I found through trial and error)
26
26
26
22
20

If the function can not find a combination equal to my specified number of
120, then, I would like the function to return the combination of numbers
that is as close to 120 without going over.

In fact, and now I'm probably asking for the impossible, I would like to see
the greatest number of combinations adding up to 120 without any number being
used more than once.

Anyone know how this can be accomplished in Excel? I would hugely
appreciate your help and suggestions.