Sort a Google Spreadsheets so it returns 'alternating' values in a column

Question

I have a list of hundreds of students that contains the following data. The only two values that will appear in the gender column (col 3) are male or female:

Current List

    fName | lName | Gender | id#
----------------------------------------
    Mike  | Jones | male   | 123456
    Todd  | Smith | male   | 987654
    Kim   | Holly | female | 753951

I want to sort the sheet so it returns alternating values (male/female) in the gender column.

Desired List

        fName | lName | Gender | id#
    ----------------------------------------
        Mike  | Jones | male   | 123456
        Kim   | Holly | female | 753951
        Todd  | Smith | male   | 987654

Answer 1

I would write a custom function for this, for example the following, which accepts a rectangular range and the column by which to sort in alternating way. In your example if would be =alternate(A2:D4, 3)

The logic is simple. The rows in which the key column is the same as in the first row are placed in arr1; the rest go in arr2. Then the array output is filled by taking rows from each of these, alternatively. It may well happen that one has more rows than the other; this is accounted for (the extra rows without a pair appear at the bottom).

If this is applied to the entire columns, like A2:D, it's best to filter out empty rows; e.g. =alternate(filter(A2:D, len(C2:C)), 3).

Custom function

function alternate(arr, column) {
  var key1 = arr[0][column-1];
  var arr1 = arr.filter(function(row) {
    return row[column-1] == key1;
  });
  var arr2 = arr.filter(function(row) {
    return row[column-1] !== key1;
  });
  var output = [], i = 0, j = 0;
  while (i < arr1.length || j < arr2.length) {
    if (i < arr1.length) {
      output.push(arr1[i++]);
    }
    if (j < arr2.length) {
      output.push(arr2[j++]);
    }
  }
  return output;
}

Answer 2

UNTESTED but seems should be possible without a script, though with a helper column instead. Assuming fName is in A1, maybe in G2 and copied down to suit:

=if(C2="male",2*COUNTIF(C$1:C2,"male")+1,0)+if(C2="female",2*COUNTIF(C$1:C2,"female"),0)

Then sort A:G on G with header row.