0

my question is:

I've a list of items, let's say "A,B,C,D" in cells A1,A2,A3,A4 and so on.

With a formula, I shuffle those items in a set of 2, so I will get a random couple as A,B or C,D or B,C etc.

What I wanna achieve is to keep an average of whatever appear shuffled, so that, for example, if the 1st shuffle give out A,B, the system count A = 1 B = 1 C = 0 D = 0 and so, the 2nd shuffle will give out C,D to make it fair. If the 1st shuffle give C,A then, 2nd shuffle should give B,D and so on.

The problem is that when I use a formula to keep track of the output, the results will be overwrited from the following shuffle values, so I will never get the real counter of appearance.

My list of items is longer of course, more than 200 items, but I will generate a list of 88 out of it to be printed randomly, so after the first print, I will get 88 items with 1, after the 2nd print 88*2 = 176, but in the 3rd print I will reach the peak of new items and so, I've to print the rest from a random selection of those

under "lista" column B you will see the vlookup that will generate a match with the current "print" tab but I don't know from this point how to make the results to be static and be added to the next shuffle

https://docs.google.com/spreadsheets/d/1Jb66PhYPsegNJYpzwrYIArLp0vTBjMtaNOqNf-qSrdA/edit#gid=0

Edit:

I guess I'm closer but for some reason, something isnt working, I've added 3 scripts:

1) Copy the list generated, with the counter:

function archive() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheetByName('Lista');      // name of source sheet
  var sourceRange = source.getRange('A1:B');    // range to copy
  var destination = ss.getSheetByName('Sheet2'); // name of log sheet
  var lastRow = destination.getLastRow();
  destination.insertRowsAfter(lastRow, sourceRange.getHeight());
  sourceRange.copyTo(destination.getRange(lastRow + 1, 1),{contentsOnly:true});
}

and it works fine BUT it continue to copy from the last line even if it's empty...I'd that it will continue from the last filled line <-- need to be fixed or i'll reach the cap

2) Script 2 (with issues):

function archive2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var destination = ss.getSheetByName('Sheet3'); // name of log sheet
  destination.getRange('A1:B').activate();
  destination.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  var lastRow = destination.getLastRow();
  var source = ss.getSheetByName('Sheet2');      // name of source sheet
  var sourceRange = source.getRange('H1:I');    // range to copy
  sourceRange.copyTo(destination.getRange(lastRow + 1, 1),{contentsOnly:true});
}

Should make a copy of columns h1:I from sheet2 to sheet3, basically it's a sorted and filtered list that's the results of the generated shuffled items. In H1:I the numbers are correct, but i dont know why, in Sheet3 the copy is wrong <-- problem to fix, if u could help me i think it will works in the end

3) To avoid to reach the max lines available, I'm using a 3rd script to clean sheet2 and start with the stored archive from sheet3:

function Clean() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet2'), true);
  spreadsheet.getRange('A:B').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getCurrentCell().setFormula('=ARRAYFORMULA(if(indirect("Sheet3!A1:A"&Sheet3!F1)<>"",indirect("Sheet3!A1:B"&Sheet3!F1),""))');
  spreadsheet.getRange('A1').activate();
};

This seems to work fine

so basically the problem to be fixed are script1 and script2 now

  • Welcome, Simone. Hint, 1) share your spreadsheet without requiring a request for permission to share. 2) when writing your question, don't require that we should have to go to the spreadsheet to get more information/elaboration. For example, the formula that shuffles the items, or the vlookup. PS: I am looking forward to working on this. – Tedinoz Nov 29 at 5:47

Your Answer

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

Browse other questions tagged or ask your own question.