Use a specific set of numbers x times to equal a set value

Anthea

New Member
Joined
Apr 5, 2011
Messages
13
Hello

Please could someone help me to do this (I have searched the internet and found nothing or more than likely, am not using the correct terminology :eek:) ...
I want to use the numbers in Column F the D2 number of times to Equal C2 (or at least as close to it as possible if the actual figure is not attainable)
i.e. Use the numbers in Column F 221 times to equal 3597 (or at least as close to it as possible if the actual figure is not attainable)Any help will be much appreciatedRegards
Anthea


Excel 2016 (Windows) 32 bit
ABCDEF
1Values RequiredRequired totalNumber of values to be output to Col ANumbers to use
2359722110
318
420
521
622
723
826
930
1031
1132
1236
1338
1440
1542
1650
1753
1854
1955
2056
2166
2267
2368
2469
2570
2674
2776
2881
2982
3084
3190
3292
33100
34102
35106
36110
37114
38116
39118
40132
41134
42138
43140
44144
45146
46152
47
48

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2
 

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,555
Do you mean something like this? (There are more than two possibilities):

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: right;;">No of times</td><td style="font-weight: bold;text-align: right;;">Number</td><td style="font-weight: bold;text-align: right;;">Total</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">172</td><td style="text-align: right;;">18</td><td style="text-align: right;;">3,096</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">48</td><td style="text-align: right;;">10</td><td style="text-align: right;;">480</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">21</td><td style="text-align: right;;">21</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="font-weight: bold;text-align: right;;">221</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">3,597</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="font-weight: bold;text-align: right;;">No of times</td><td style="font-weight: bold;text-align: right;;">Number</td><td style="font-weight: bold;text-align: right;;">Total</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">201</td><td style="text-align: right;;">10</td><td style="text-align: right;;">2,010</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">1</td><td style="text-align: right;;">18</td><td style="text-align: right;;">18</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">17</td><td style="text-align: right;;">81</td><td style="text-align: right;;">1,377</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">1</td><td style="text-align: right;;">92</td><td style="text-align: right;;">92</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">1</td><td style="text-align: right;;">100</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="font-weight: bold;text-align: right;;">221</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">3,597</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)"></p><br /><br />

Are you looking for a solution to this specific problem? All possible solutions? Or the solution(s) to a more general problem?

Assuming I have interpreted correctly, this specific example is relatively easy, as 3,597/221 = approximately 16 on average, which is almost at the end of your list of numbers. If you choose a total bigger than 3,597, it may become impractical to test all possible combinations.
 

Forum statistics

Threads
1,082,305
Messages
5,364,401
Members
400,800
Latest member
germanpbv

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top