I'm confused by your comments:
1. Changing the count breaks the result where the total distribution is not close to/equal.
You don't explicitly change the the counts anywhere. Your only actions on the [Source] sheet are to fill out the users/attendees block in F5:H34 (and add to the end of that table if you need more room for additional users). Formulas do all of the counting and determine whether there are an even or odd number of attendees, and hence whether the two groups to be formed are of equal size or whether their sizes differ by one.
2. With 20 or above count, it only populates group 2
After adding/deleting x's to the "In Attendance" columns to reflect a new scenario,
the worksheet will not automatically update. You still need to follow the instructions in J3:K3 and copy the new KeyCode/Weight table (that reflects the revised attendee list) and paste it into A2:B2 and down as Values only. This can be done by copying the range that covers the updated KeyCode/Weight table, selecting cell A2, and then Paste>Paste Special>Values...and then delete any leftover content below the freshly pasted information (which would be necessary if a shorter block of cells were pasted over a previous longer block). And then the Control3 macro needs to be re-run. Only then, if any relevant solutions are found will the helper columns and the Group1/2 columns populate with meaningful results.
3. The end result for other combination of users is providing a group population of 8 in Group 1 and 10 in Group 2.
This isn't possible if the sheet is used as I described above. If one or more solutions are found with the correct number of values within the 400 rows returned to the [Result] sheet, the groups will be balanced in number to within 1, depending on whether there is an even or odd number of attendees.
About your description of the general approach here, I wouldn't say that the groups are filled with random people and that various combinations are generated randomly.
1. Sum the total score of present users.
2. Divide the total score of present users by 2 to create the target sum value.
3. Count the number of present users.
4. Divide the number of present users by 2 to determine the size of Group 1 and Group 2.
5. Assign each present user an easier to reference key code, not a random number, but a unique letter-based key code that serves as an abbreviated surrogate for their name to facilitate subsequent processing. The assignment of key codes to names is arbitrary.
6. For the subsequent processing, use an algorithm to systematically step through combinations of users (keycodes) and associated scores to determine which ones produce a sum "close" to the target determined in step 2. The algorithm used could vary. The one I used was developed by Tony Dallimore about 8 years ago for a different purpose and it delivers results to sheet [Result]. This class of problem is known as a "subset sum" problem, where one wishes to determine which subset of numbers in array "a" produce a sum "s" (or near a sum "s"). That's what the code does. Your problem imposes an additional constraint and could be described as a "subset sum of k elements" problem where each subset of values considered must consist of "k" elements (i.e., INT(n/2), where "n" is the number of attendees). In the results table, you see indications that the code was designed for the first description (subset sum) rather than the second description with the additional constraint, as most of the results involve subsets consisting of more or fewer values than required. The length of the results table is controlled by the RowRsltArrMax variable. The code initially populates the results table with the first RowRsltArrMax (whatever number that is set to) results, regardless of whether they are very close to the target sum. Then for each subsequent subset of numbers, their sum is compared to the worst solution in the results table. If the new subset is an improvement, it becomes part of the results table and the worst solution is pushed off the table. If the new subset is not an improvement over the worst solution in the table, the new subset is discarded. Then the next subset of values is considered, and so on. The algorithm followed is a variant of a Branch & Bound method that attempts to improve efficiency by avoiding further exploration down certain branches when logic dictates that considering certain larger or smaller values will not offer any improvement. I had set RowRsltArrMax=400, but for a larger number of attendees, it is conceivable that all 400 results returned will not include any that have the correct number of values required to form a group because many other shorter combinations may be encountered first that are deemed "close enough" before the algorithm ever begins adding in more values. In that case, you won't get a meaningful answer on the [Source] sheet. I set up a scenario with 24 attendees and edited the code to change RowRsltArrMax=6000 and edited the helper column formulas to look at those 6000 rows. This change comes at a cost, as the spreadsheet is noticeably slower, although that may be tolerable if you have time to wait (perhaps 1-3 minutes for the code to run, and considerably longer if RowsRsltArrMax is set to even higher values). For the sample problem with n=24, among the 6000 results returned by the code, only 150 involved 12 values. The target sum was 74.553. Of those 150 results with 12 values, their sums ranged from 74.316 to 76.316, and the best sum differed from the target by only 0.026...and that was obtained by 3 combinations of 12 attendees. Based on the requirement given in your first post...
Each group should have as close to equal weightage distribution as possible.
...this is what I assumed you wanted--an approach that gives priority to obtaining the target sum.
But your question in post #13...
How can one randomize this with different combination coming up with a click/refresh?
...suggests that you do not have a firm requirement for an optimized solution aimed at achieving sums equal to each other. You apparently have something else in mind involving distributing the attendees differently.
Since beginning this response, I see that you found a satisfactory solution elsewhere. Thank you for following up with that update, and I'm glad you have a solution that suits your purposes.
If you are interested in investigating this approach further, my latest file is found at the link below. After running the code, the user still needs to confirm that the helper columns O:S are populated. If they are not, then RowsRsltArrMax needs to be set even larger and three formulas need to be updated to correctly reference the results on the [Result] sheet. Then the user needs to select among any of potentially several equivalent solutions by using the dropdown selector in cell R2. That pulls a particular result from the [Result] sheet and shows what the two groups would look like in columns L:M.
Shared with Dropbox
www.dropbox.com
