How to sort a range alternating Male Female

Question

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

Answer 1

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.