0

I have a sheet for my classes, containing a seating chart and a list of student names. With data sorting I am able to generate quickly a new random seating chart, but I would like to also do this based on gender, in an alternating way (boy - girl). The standard option to sort based on gender gives me the student names sorted with all the girls (F) first and then all the boys (M). Can someone help me create a formula or script for this? For reference; column "U" is Student Name, column "V" is Gender (F or M)

File: the Seating Chart

1

1 Answer 1

0

Use sort() and sequence(), like this:

=arrayformula( 
  lambda( 
    girls, boys, 
    sort( 
      { girls; boys }, 
      { sequence(rows(girls)) * 2; sequence(rows(boys)) * 2 + 1 }, 
      true 
    ) 
  )( 
    filter(U2:V, V2:V = "F"), 
    filter(U2:V, V2:V = "M") 
  ) 
)

To shuffle the order of girls and boys while alternating between genders, Insert > Checkbox in cell X1 and use this formula:


=arrayformula( 
  lambda( 
    girls, boys, refresh, 
    lambda( 
      randGirls, randBoys, 
      sort( 
        { randGirls; randBoys }, 
        { sequence(rows(randGirls)) * 2; sequence(rows(randBoys)) * 2 + 1 }, 
        true 
      ) 
    )( 
      sort(girls, randarray(rows(girls)), true), 
      sort(boys, randarray(rows(boys)), true) 
    ) 
  )( 
    filter(U2:V, V2:V = "F"), 
    filter(U2:V, V2:V = "M"), 
    X1 
  ) 
)

To get a new random order, click the checkbox.

In the event your spreadsheet locale uses commas , as decimal separators, you will have to use semicolons ; as formula argument separators. In other words, replace all the commas in the above formulas with semicolons.

5
  • Thank you, where do I paste this formula? In the Apps Script? Jan 22 at 14:41
  • It's a normal formula, albeit a bit longer than your average sum(). Paste it in row 2 of a free column in your spreadsheet. Jan 22 at 15:06
  • Thanks, I did that, and it does sort alternating that column. But I would like the columns U and V to be sorted, because they in turn are referenced in the seating chart. Let me include my Sheet, so you can see what I mean. docs.google.com/spreadsheets/d/… Whenever I sort U, V or W, the seating chart on the left is updated. This goes fine with random (W) or alphabetically (U) but not yet based on alternating gender (V). Jan 22 at 15:16
  • Sorry, I changed the access, you should be able to open it now. Jan 22 at 15:45
  • To make your seating chart refer to the shuffled alternating results, modify the formulas that build the seating chart so that they point to column X instead of column U. You can modify the formulas manually or through Edit > Find and replace > Also search within formulas. Jan 22 at 16:21

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.