title: Assign data to a group in a random order, respecting constraints in Excel
body: <p>I have about a thousand rows of data that I would like to assign to random groups that follow some rules. I have tried a lot of ways with no success, and I have not found any answers online so I'm hoping you can help.</p>
  
  <p>Basically, I have rows of data sort of like this:</p>
  
  <pre><code>Key   Description   Weight    Group
  1     Orange        0.987
  2     Apple         0.876
  3     Melon         0.765
  4     Peach         0.654
  5     Mango         0.432
  6     Banana        0.321
  7     Kiwi          0.219
  </code></pre>
  
  <p>And so on, always sorted from highest to lowest weight...
  Now, I want to divide into groups that follow two set of rules: exact amount of Keys in each group and an approximate sum of the weight. So, let's say there are 2 groups for this example that must follow the rules:</p>
  
  <pre><code>Group     # of keys    Sum of weight
  1         4            2.8
  2         3            1.6
  </code></pre>
  
  <p>So, an example output would be:</p>
  
  <pre><code>Key   Description   Weight    Group
  1     Orange        0.987     1
  2     Apple         0.876     2
  3     Melon         0.765     1
  4     Peach         0.654     2
  5     Mango         0.432     1
  6     Banana        0.321     1
  7     Kiwi          0.219     2
  </code></pre>
  
  <p>So here there are 4 fruits in group 1 with a resulting weight of 2.51, and 3 fruits in group 2 with weight of 1.75.</p>
  
  <p>The goal here is to match the # of keys in a group exactly to what's expected and that the sum of the weight is similar to what's expected (maybe with a surplus of up to 10%). PLUS, the groups shouldn't follow any apparent order (the top keys always for group 1, for example), but instead be as random as possible.</p>
  
  <p>I have tried with formulas and so far I've come up with ways to match the # of keys for each group, but the weights are VERY different from what's expected. I would prefer that it is resolved with formulas, but if VBA is needed it's no problem. </p>
  
  <blockquote>
    <p>Tl;dr: I'm looking to assign groups to a set of rows making sure that
    the number of rows for each group is constrained by user inputs, and
    that the sum of a column for each group is similar to a given
    "goal"(defined by user) for each group. Preferably without VBA.</p>
  </blockquote>