1

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

...
  • 1
    Hi Ed, welcome to Webapps.se! Your question is a good one, thanks for describing the desired output. However, I'm not sure what you mean by 'dynamic based on unique rooms, in case you add a new room'. Could you edit your question and add some detail to that part? – Vidar S. Ramdal May 10 at 7:44
  • I think you understood the script works very well. THANKS!!!!! – Ed Urag May 10 at 22:17
1

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.

  • You're so fast! THANKS! Vidar, this works for my initial request. I just edited the post since I forgot that I need to do it with multiple columns. Maybe you can help me again? :) – Ed Urag May 11 at 6:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.