Google Sheets – Sort to Return Alternating Values in a Column

google sheetssortingworksheet-function

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

Best Answer

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;
}
Related Topic