Hello, I am trying to automate a process and have tried various different platforms but seems that most roads lead to Excel. I'd like to take a column of numbers and group the 2 closest numbers that sum between 511 and 592. Once 2 numbers are grouped, they fall off the column of data and excel iterates through the remaining column data for the next highest group between 511 and 592. If there are no 2 numbers that add to between 511 and 592, I'd like to sum 3 numbers that add between 483 and 510. Again, once a number is grouped, it falls off the list and iterates through until no numbers are left that group between 483 and 510. Once there are no numbers that sum between 483 and 510, group 4 numbers together that sum between 420 and 483. If no numbers, group 5 numbers that sum less than 420. If there is 5 or less numbers left on the list, sum them. See example below.
| ID | Number | Group | Sum of Group |
1 | 300 | 1,2 | 580 |
2 | 280 | ||
3 | 270 | 3,4 | 535 |
4 | 265 | ||
5 | 250 | 5,7,28 | 502.5 |
6 | 220 | 6,8,13 | 505 |
7 | 180 | ||
8 | 162.5 | ||
9 | 157.5 | 9,10,17,27 | 482.5 |
10 | 150 | ||
11 | 140 | 11,12,14,18 | 483 |
12 | 131.5 | ||
13 | 122.5 | ||
14 | 114 | ||
15 | 105 | ||
16 | 102.5 | ||
17 | 100 | ||
18 | 97.5 | ||
19 | 95 | 19,23,24,25,26 | 420 |
20 | 92.5 | ||
21 | 90 | ||
22 | 87.5 | 15,16,20,21,22 | 477.5 |
23 | 85 | ||
24 | 82.5 | ||
25 | 80 | ||
26 | 77.5 | ||
27 | 75 | ||
28 | 72.5 | ||
29 | 40 | ||
30 | 20 |
