In Excel, how can I ensure that N randomly selected columns from a named range have unique values across each row?

Question

Background

I am able to select values at random from a named range of strings in one sheet ("Sheet2") and populate a column called: MyColumn in a separate sheet ("Sheet1") using =INDEX(aNamedRange,RANDBETWEEN(1,ROWS(aNamedRange))).

It looks like this:

|            MyColumn           |
|-------------------------------|
| RandomlySelectedValue_1, Col1 |
| RandomlySelectedValue_2, Col1 |
|              ...              |
| RandomlySelectedValue_N, Col1 |

I have made two other columns: MyColumn2 and MyColumn3.

Question

As RandBetween executes upon refresh is there a way to ensure that RandomlySelectedValue_1, Col1 does not equal RandomlySelectedValue_1, Col2?.

In other words:

This is the expected behavior, because all the randomly selected strings in the columns are distinct across the same row.
This is what's happening, there is no check to prevent any randomly selected duplicates in the same row.

What I've tried

I made this VBA Script:

Sub UniqueRandoms()
Do
    Worksheets("Sheet1").Range("AB7").Value = WorksheetFunction.Index(Range("FileName.xls!aNamedRange"), WorksheetFunction.RandBetween(1, Sheets("Sheet2").Rows(Range("FileName.xls!aNamedRange"))))
    Worksheets("Sheet1").Range("AC7").Value = WorksheetFunction.Index(Range("FileName.xls!aNamedRange"), WorksheetFunction.RandBetween(1, Sheets("Sheet2").Rows(Range("FileName.xls!aNamedRange"))))
    Worksheets("Sheet1").Range("AD7").Value = WorksheetFunction.Index(Range("FileName.xls!aNamedRange"), WorksheetFunction.RandBetween(1, Sheets("Sheet2").Rows(Range("FileName.xls!aNamedRange"))))
Exit Do

Loop Until Worksheets("Sheet1").Range("AB7").Value <> Worksheets("Sheet1").Range("AC7").Value And Worksheets("Sheet1").Range("AD7").Value <> Worksheets("Sheet1").Range("AC7").Value And Worksheets("Sheet1").Range("AB7").Value <> Worksheets("Sheet1").Range("AD7").Value

End Sub

but I'm receiving a Run-time error '1004': Application-defined or object-defined error.

Is the above script the best way to solve the problem or is there a simpler solution (that is potentially generalizable to any number of columns)?

Welcome to StackOverflow. If the range were 1-6, say what you'd see on the face of the die, would it be non-random to have, say, three 4s come up? — rajah9
Which worksheet is active when you run this VBA code? The 1004 would like raise if Sheet1 is active, but might raise if Sheet2 is active. Is the named range local to the worksheet or to the workbook? — David Zemens
Also worth mentioning: the random functions in Excel are pseudo-random at best. Also, no, there is nothing in the random functions that would prevent duplicates, although duplicates may be unlikely given a large enough range (e.g., RandBetween(1,100000) is less likely to have a dupe than RandBetween(1,5), if you strictly need to prevent duplicates, then you'll need to implement some custom approach with VBA — David Zemens
So the named range is local to one sheet ("Sheet2"), but the columns I want to populate are in a separate sheet ("Sheet1"). — Kriarvi95
So create a collection, and a variable to hold your random number. check to see if the number is in the collection, if not add it to the sheet and the collection, generate the next number, check if it is in the collection, if so generate again if not add to sheet and generate next number, rinse and repeat. May want to have a duplicate counter checker so if you run into the same number x amount of times you can exit the routine. — Sorceri

CallumDA CallumDA · Accepted Answer · 2017-01-09T22:15:10

I went about creating something similar. I set my file up like this:

and I was able to take values in A1:A7 and randomly place them in the columns at the top but specifically with no repeats.

I used a collection because the ability to add/remove items made it much easier to ensure I didn't double up. Once a value is output to the worksheet I removed it from the collection.

Sub RandomPlay()
    Dim inputRange As Range
    Set inputRange = Sheet1.Range("A1:A7")

    Dim inputCollection As Collection
    Set inputCollection = New Collection

    'populate collection with values from the input range
    Dim rng As Range
    For Each rng In inputRange
        inputCollection.Add rng.Value
    Next rng

    Dim randomPick As Integer
    Dim i As Integer

    Dim result As Variant
    ReDim result(1 To inputCollection.Count)

    'pick randomly from collection > add to result array > remove from collection > repeat
    For i = 1 To inputCollection.Count
        randomPick = Int(Rnd() * inputCollection.Count) + 1
        result(i) = inputCollection(randomPick)
        inputCollection.Remove randomPick
    Next i

    Sheet1.Range("C2:I2").Value = result

    Set inputCollection = Nothing
End Sub

I'm not sure if this data setup is quite the same as yours but the method shown here should be helpful either way.

In Excel, how can I ensure that N randomly selected columns from a named range have unique values across each row?

Background

Question

What I've tried

1 Answers