0

I'm making a fairly simple spreadsheet to organize a tournament of sorts and wanted to find a way to randomize some variables using sheets.

The idea is that there are 25 possible maps which can be chosen, but the user will determine how many maps will be played (within the sheet), for example 5. Ideally I'd have a cell which would take the number of maps to be played (in this case 5) and will then print the 5 random maps in sequence from the range of map 1, to map 25.

Eg -

D24 = 5 (Number of maps)

D26 = 3,16,21,13,6

Would there also be a way to have it so no duplicates can occur?

I tried something like this: =TRUNC(RAND() * (25-1)+1) however couldn't find a way to make it loop.

Peter O.
  • 32,158
  • 14
  • 82
  • 96
Christopher Georgiou
  • 25
  • 2
  • 7

1 Answers1

1

Try:

=JOIN(", ", ARRAY_CONSTRAIN(SORT({A2:A26, RANDARRAY(COUNTA(A2:A26), 1)}, 2, 1), D2, 1))

enter image description here

kishkin
  • 5,152
  • 1
  • 26
  • 40
  • That worked, thanks so much! Is there any way I can have it only randomize when the number of maps cell is updated, rather than when anything on the sheet changes? – Christopher Georgiou Oct 06 '21 at 12:55
  • @ChristopherGeorgiou either using a script for the task or by using a undocumented function `WHATTHEFOXSAY()` - [see here](https://stackoverflow.com/a/66207070/279806) – kishkin Oct 06 '21 at 16:17
  • I ended up using a script and copying the values to a separate cell when a "button" was pressed that did the trick! I do have another small question that you might be able to help me with. I have totals of the points per player and wanted a way to add an additional 20 points to the team with the highest point earner? (Screenshot should hopefully make this clearer). Tried using large to just identify who it is but Im getting a #ERROR! message for a formula parse error https://gyazo.com/1403e2e34fe1446870dc80a406e02965 – Christopher Georgiou Oct 06 '21 at 16:58
  • @ChristopherGeorgiou If you came up with a script solution to avoid further randomizations, would you consider posting it as an answer? – Iamblichus Oct 07 '21 at 08:43
  • @Iamblichus Sorry for the late reply! So I used the method Kishkin suggested, however have the results show in a hidden cell. I then added an Apps Script attatched to a button to copy the values and paste them into a different cell so that only updates once the button is pressed - `function mapSelection() { var maps = SpreadsheetApp.getActiveSheet().getRange(28,3).getValues(); Logger.log(maps); var cell = SpreadsheetApp.getActiveSheet().getRange(30,3); cell.setValue(maps); }` – Christopher Georgiou Oct 23 '21 at 22:03