Google-sheets – Sort Google Spreadsheets so it returns ‘alternating’ values in a column

google sheetssortingworksheet-function

How can I sort a random list in Google Sheets so it returns alternating values?

The current random list looks like this:

fName   lName   Room ID
-----------------------------------
Sara    Don     104  524134
Mark    Don     104  323124
Rose    Jones   101  145456
Mike    Jones   101  123456
Todd    Smith   103  321313
Kim     Holly   102  753951
Paul    Holly   102  745951
Dave    Smith   103  355353

The desired list would look like this:

fName   lName   Room ID
-----------------------------------
Rose    Jones   101  145456
Kim     Holly   102  753951
Todd    Smith   103  321313
Sara    Don     104  524134
Mike    Jones   101  123456
Paul    Holly   102  745951
Dave    Smith   103  355353
Mark    Don     104  323124

...

Best Answer

Not an easy problem to solve with spreadsheet formulas, so I came up with something in Google Apps Script that seems to work.:

function alternateSort(data, sortColumnPos) {
  const sortColumnIndex = sortColumnPos - 1;

  const groups = {};

  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    var rowGroup = row[sortColumnIndex];
    if (groups[rowGroup]) {
      groups[rowGroup].push(row); // This is not the first row for this group, add it to existing array
    } else {
      groups[rowGroup] = [row]; // This is the first row for this group, create a new array with it
    }
  }

  var result = [];
  var groupKeys = Object.keys(groups).sort();

  var groupSizes = groupKeys.map(function(groupKey) {return groups[groupKey].length});

  var maxGroupSize = groupSizes.reduce(function(max, val) { return val > max ? val : max}, 0);  

  for (var i = 0; i < maxGroupSize; i++) {
    for (var g = 0; g < groupKeys.length; g++) {
      var groupKey = groupKeys[g];
      var group = groups[groupKey];
      if (group[i]) {
        result.push(group[i]);
      }
    }
  }
  return result;
}

To use this script, go to your spreadsheet, click Tools → Script Editor and paste the above script. Save.

Now find the cell where you want the results to appear. In that cell, enter =alternateSort(A2:D9; 3) where you replace A2:D9 with the area where your input data is, and 3 with the number of the column which will be used for sorting.

I created an example spreadsheet to demonstrate: https://docs.google.com/spreadsheets/d/1RLNByCIIW6lgJWrbXNaIQpSj-fD3yqBu4YR4DQJNGIY/edit?usp=sharing - feel free to copy it to your own drive for experimentation.

Related Topic