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

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
``````

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[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;
}
``````

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.