Non Repeated Random value from Range

Question

Need to amend my script so that the random values generated from my range won't repeat when the function "myFill" is triggered. The range currently has 32 values - but only 5 non repeated values are required each time when triggered. Those values can be included again next time the function is triggered.

Link attached.

As an overview, the Script below is used in conjuction with these formula on the spreadsheet

   Cell B2 =myFill(1,5,"H2:H33",J18) 
   Cell B3 =iferror(ArrayFormula(vlookup(B2,$H$2:$I$33,2,0)))

Appscript:

    function tst(){
    var rows = 1
    var cols = 5
    var range = "H2:H33"
    myFill(rows, cols, range)
    }

   function myFill(rows, columns, range) {
   var ss= SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getActiveSheet();
   Logger.log(rows)
   Logger.log(columns)
   Logger.log(range)
   var rng = sheet.getRange(range).getValues()
   var output1 = [], l = rng.length;
   for(var i=0; i<rows; i++) {
   var output2 = [];
   for(var j=0; j<columns; j++) {
   output2.push(rng[Math.floor(Math.random() * l)][0]);
   }
   output1.push(output2);
    }   
   return output1;  
   }

Random Value Test

Thanks to both you guys. I only tested Jag's amendment on my actual sheet and it has worked as needed so I've gone with that. Thanks again. — MJM
– MJM, Commented Mar 17, 2017 at 23:01

Max Makhrov · Accepted Answer · 2017-03-21 08:31:25Z

1

I wondered if it's possible to do it with formulas. The resulting formula is:

=ArrayFormula(VLOOKUP(QUERY(UNIQUE(RANDBETWEEN(ROW(INDIRECT("A1:A"&COUNTA(A:A)*10))^0,COUNTA(A:A))),"limit 5"),{ROW(INDIRECT("A1:A"&COUNTA(A:A))),FILTER(A:A,A:A<>"")},2,0))

Please look at this Sample file with explanations on how the formula works.

answered Mar 21, 2017 at 8:31

Max Makhrov

18.9k6 gold badges65 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jack Brown · Accepted Answer · 2017-03-17 16:40:50Z

This code will give you unique set of letters i.e. no repeating set for each run. However, in my brief executions of the script never once encountered a repeat random number anyway!

function myFill(rows, columns, range) {
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  Logger.log(rows)
  Logger.log(columns)
  Logger.log(range)
  var rng = sheet.getRange(range).getValues();
  var output1 = [], l = rng.length;
  for(var i=0; i<rows; i++) {
    var output2 = [];
    for(var j=0; j<columns; j++) {
      var isUnique = false
      do {
      var index = Math.floor(Math.random() * l) 
      if(rng[index][0] !== 0){  //Check if the value has been already been used. 
        output2.push(rng[index][0]);
        rng[index][0] = 0; // Set a used value to zero
        isUnique = true; 
      }
      } while (!isUnique) 
    }
    output1.push(output2);
  }   
  return output1;  
}

The way I achieve to get unique letters is by setting the value of the rng array to 0 if that particular value at that index has already been used. Then check index each time to check if it is zero if it not then use that value else generate a new random index.

Cooper · Accepted Answer · 2017-03-17 14:23:31Z

0

Well I'm not sure what your trying to accomplish because your not doing anything with the return from myFills but this is a little more likely to work for you. Your were using "rng" as a range but getValues returns an array (2D). BTW random does not mean non repeating.

function tst()
{
    var rows = 1;
    var cols = 5;
    var range = "H2:H33";
    myFill(rows, cols, range);
}

function myFill(rows, columns, range) 
{
   var rows = (typeof(rows) !== 'undefined')? rows : null; 
   var columns = (typeof(columns) !== 'undefined')? columns : null; 
   var range = (typeof(range) !== 'undefined')? range : null; 
   var sheet = SpreadsheetApp.getActiveSheet();
   var rngA = sheet.getRange(range).getValues();
   var outputA = []; 
   var l = rngA.length;
   for(var i=0; i<rows; i++) 
   {
     outputA[i] = [];
     for(var j=0; j<columns; j++) 
     {
       outputA[i][j]=Math.floor(Math.random() * l);
     }
   }   
   return outputA;  
}

answered Mar 17, 2017 at 14:23

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

1 Comment

MJM Over a year ago

"BTW random does not mean non repeating."....... yes, and as it is in a defined list its not random either :)

Collectives™ on Stack Overflow

Non Repeated Random value from Range

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related