title: Preference Votes Data to Groups
body: <p>I have a problem creating a formula or VBA macro that sorts 'preference voting' data into appropriate groups for students selecting summer camp electives. Historically, we've done the voting and sorting on paper, and i'd like to move to something a little less time consuming for the many, many rounds of electives we do at camp.</p>
  
  <p>Ive created a form they fill out, which gives me a spreadsheet with their elective preferences. it looks like this </p>
  
  <pre>
  Kids    A   B   C
  1001    2   3   1
  1002    3   1   2
  1003    3   1   2
  1004    3   1   2
  1005    3   1   2
  1006    3   1   2
  1007    3   2   1
  1008    3   2   1
  1009    2   1   3
  1010    3   1   2
  1011    2   1   3
  </pre>
  
  <p>what id like to be able to do is run a macro or (even better) a dynamic function that sorts the voters into categories - like this</p>
  
  <pre>
  A       B       C
  1001    1002    1007
  1010    1003    1008
  1011    1004    1009
          1005    
          1006    
  </pre>
  
  <p>basically - elective A has no first choice votes so its initial count = 0. Elective B has 8 first choice votes, so its initial count is 8, elective c has 3 first choice votes so its initial count is 3. I need these to be at least close to balanced (plus i actually have over 100 students), so we have 2nd choices also (3rd is a strike). so the minimum count for each group needs to be 1/4 + 1 total voting population.</p>
  
  <p>Obviously no solution is perfect, because theres an inherently subjective choice about who gets moved from their first choice to their second, but any help would be appreciated.</p>
  
  <p>If theres something in stat math that would point me in the right direction that would help too. ive tried googling this, but all references to voting systems i can find assume i want to anonymise the data, which is the opposite of what i need.</p>
  
  <p>ive tried vlookups and indexing, but the formulas quickly get unwieldy, and dont seem to do what i need anyway. SORT functions seem to be the way to go, but i cant wrap my head around the syntax of them (using just visual sort is how ive rendered the above sorting.) RANK doesnt seem to offer what im looking for.</p>