1

I have a google sheet with three headers: name, state, and specialty. There are 200 entries in the sheet. Is there an easy way I could have the sheet create groups of four in which there are no duplicates? In other words, I'd like teams of four in which no two are from the same state or have the same specialty. And I'd like to be able to manually place the leftovers into teams (i.e. I don't want to lose any of the 200 entries in the process).

1

This is a too complex task for spreadsheet functions. I wrote a custom function assignteams for this purpose, which is used as =assignteams(range) where range is the 3-column table of names, cities and specialties.

The function does not use any fancy combinatorics looking for "optimal" arrangements (which I suspect is a hard problem). It simply compares each person against the list of existing teams: if there is room in the team (teams[j].length < 4) and the person fits (fits(data[i], teams[j])), they are added to the team. If no suitable team exists for them, they are placed in a new team.

You may want to copy the output and paste it somewhere as values (Ctrl-Shift-V) so that it can be sorted by team number, edited manually, etc.

function assignTeams(data) {
  var teams = [[]];
  var output = [["Name", "City", "Specialty", "Team"]].concat(data);
  for (var i = 0; i < data.length;  i++) {
    var placed = false;
    for (var j = 0; j < teams.length; j++) {
      if (teams[j].length < 4 && fits(data[i], teams[j])) {
        teams[j].push(data[i]);
        output[i+1].push(j+1);
        placed = true;
        break;
      }
    }
    if (!placed) {
      teams.push([data[i]]);
      output[i+1].push(teams.length);
    }
  }
  return output;                 
}

function fits(person, team) {
  var fit = true;
  for (var i = 0; i < team.length; i++) {
    if (person[1] == team[i][1] || person[2] == team[i][2]) {
      fit = false;
      break;
    }
  }
  return fit;
}

Sample input and output

Input (name / city / specialty):

q   zz  ww
w   xx  ee
e   xx  rr
r   xx  tt
t   cc  rr
y   zz  ee
u   vv  ww
i   bb  tt
o   nn  yy
p   cc  ee
l   mm  uu
k   bb  rr
j   jj  ii
h   gg  oo
g   dd  pp
f   xx  ee
d   cc  ww
s   xx  rr
a   xx  tt
z   cc  rr
x   zz  ee
c   cc  rr
v   mm  ii
b   bb  yy
n   jj  uu
m   xx  ww

Output:

Name    City    Specialty   Team
q   zz  ww  1
w   xx  ee  1
e   xx  rr  2
r   xx  tt  3
t   cc  rr  1
y   zz  ee  2
u   vv  ww  2
i   bb  tt  1
o   nn  yy  2
p   cc  ee  3
l   mm  uu  3
k   bb  rr  3
j   jj  ii  4
h   gg  oo  4
g   dd  pp  4
f   xx  ee  4
d   cc  ww  5
s   xx  rr  5
a   xx  tt  6
z   cc  rr  6
x   zz  ee  5
c   cc  rr  7
v   mm  ii  5
b   bb  yy  6
n   jj  uu  6
m   xx  ww  7

Sorting the output by team number, one can see that all teams have 4 members except the 7th one.

| improve this answer | |
  • I will try this! Thanks so much! – Jeanine Jul 25 '17 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.