pop a random value from a list of cells

Question

I have a question that is similar to this one, but a little different:

Let's say I have data like this:

CAR NAME   COLOR  LIST OF COLORS
Car1         ?        Red
Car2         ?        Blue
Car3         ?        Green
Car4         ?        Black

and I want to randomly distribute all of the colors to all of the cars without repetition, i.e.

CAR NAME   COLOR  LIST OF COLORS
Car1       Green        Red
Car2       Black        Blue
Car3       Blue         Green
Car4       Red          Black

Is there a way to have a cell randomly select from a list excluding values already input in another range?

You need to use VBA to perform this efficiently. A formula can iterate but native worksheet formulas do not handle undetermined scope well. — , Sep 29 '15 at 22:34
Add a column with `=RAND()` to the right of "List of Colors" and sort the two columns on the new column. — Tim Williams, Sep 29 '15 at 22:40

score 1 · Accepted Answer · answered Sep 29 '15 at 22:48

1

In D2 enter:

=RAND()

and copy down. In B2 enter:

=INDEX(C$2:C$5,MATCH(LARGE(D$2:D$5,ROW()-1),D$2:D$5,0))

and copy down:

answered Sep 29 '15 at 22:48

Gary's Student

95,722
10
59
99

score 0 · Answer 2 · edited May 23 '17 at 12:29

One way is to shuffle the array. It's fairly easy to do in JavaScript.

How to randomize (shuffle) a JavaScript array?

shuffle = function(o){
    for(var j, x, i = o.length; i; j = Math.floor(Math.random() * i), x = o[--i], o[i] = o[j], o[j] = x);
    // rotate array vertical
    var vArray = [];
    for (var i = 0; i < o.length; i++) {
      vArray.push([o[i]]);
    }
    return vArray;
}

Example with spreadsheet:

http://dtab.io/sheets/560b7f5d457aff1e1fe625d2

pop a random value from a list of cells

2 Answers2