I am creating a multiple choice (see column A) and I want it to be randomized (see column C). I saw a formula in the internet, modified it, and it really does randomized the contents of each cell in column A. But one problem is that, the number of multiple choice options vary. For example, in row 2 I only have 3 options; in row 3 I have 4 options; in row 4 I have 5 options. The problem is, in order for the formula to work, the range (see the highlighted yellow part of the formula) must also coincide the number of options. I have tried my best but I'm really stuck on this. I can't find a way to make it dynamic based on the number of multiple choice options. Can you help me with this? Thank you so much!
Here's the link to my Google Sheet https://docs.google.com/spreadsheets/d/1RnN7CUiuFt6m2q6ii3B6pEG9kHx2ofYvbNqWj3TZV9c/edit?usp=sharing
-
Welcome to Web Applications Stack Exchange. Google Forms supports randomization in multiple choice questions, so if you are creating a quiz of some sort, you may want to look into Forms instead of Sheets.– doubleunarySep 23, 2021 at 16:54
1 Answer
This answer is an adaptation of an answer by @Calculuswhiz to Shuffle comma-separated list of values in one cell (Google Sheets) on StackOverflow.
There is one key difference between the scenario of this question and the StackOverflow question. In StackOverflow, the values were already in a comma separated list. In this question, the values are in a list separated by line feeds. Ironically this makes it extremely easy to adapt the StackOverflow question.
Use this formula, and copy down as many rows as necessary. Alternatively, adapt the single arrayformula in the StackOverflow answer.
=ArrayFormula(JOIN(", ",Array_Constrain(SORT(TRANSPOSE({SPLIT(join(",",split(A2,char(10))), ", ");RANDARRAY(1,COUNTA(SPLIT(join(",",split(A2,char(10))), ", ")))}),2,),COUNTA(SPLIT(join(",",split(A2,char(10))),", ")),1)))
Convert list to comma separated list
The initial values in Column A are separated by a line feed (char(10)). To convert this to a comma separated list involves the following steps:
- remove the line feed:
split(A2,char(10)), then - join the values:
join(",",split(A2,char(10)))
Since the StackOverflow formula references cell A1, it is a simple matter to substitute the join formula at each reference to Cell A1 in the StackOverflow formula.
SAMPLE
