Google Sheets – How to Select a Subset of Random Elements from a List

google sheets

Example:

Select two random elements from the list below. A chosen element should not be chosen twice.

enter image description here

Best Answer

There is a RANDOM function in Google Sheets, and it could definitely be used here, but it would be a messy formula. I prefer writing these kind of things as scripts.

Click Tools → Script Editor, and paste this code:

function selectRandomElements(fromValueRows, count) {
  var pickedRows = []; // This will hold the selected rows
  for (var i = 0; i < count && fromValueRows.length > 0; i++) {
    var pickedIndex = getRandomInt(0, fromValueRows.length);
    // Pick the element at position pickedIndex, and remove it from fromValueRows. See https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/splice
    var pickedRow = fromValueRows.splice(pickedIndex, 1)[0]; 
    // Add the selected row to our result array
    pickedRows.push(pickedRow);
  }
  return pickedRows;
}

function getRandomInt(min, max) { // From https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Math/random
  min = Math.ceil(min);
  max = Math.floor(max);
  return Math.floor(Math.random() * (max - min)) + min;
}

/* This is merely to test our function. It can be run directly from the script editor. Open View -> Log to see the results */
function testRandomElements() {
  var fromValues = [["Bob"], ["Jenny"], ["Alex"], ["Steven"], ["Monique"]];
  var result = selectRandomElements(fromValues, 2);
  Logger.log(result);
}

The function in the script basically takes a list of values and a count as inputs, and returns count randomly selected values.

This will give you access to a function selectRandomElements, which you can use from your spreadsheet, e.g. 

=selectRandomElements(A2:A6; 2)

This will give you two random values from the A2:A6 range.

See the example spreadsheet I have setup to demonstrate.

Note: The random selection will only happen once. That is, as long as the input data, and the formula, is the same, the two selected rows will be the same. If you want to rerun the selection at intervals, see this answer for a hint.

Related Topic