Randomly select a set of values from a different column with no duplicates

[curtpoz]

I am trying to randomly select a set of values from another column without duplicates. The formula I am using below works for randomly selecting values from the table in Column A, but it often returns duplicate values. Is there any way to use this without doing the whole extra "helper" =RAND() column route?


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Location
[/TD]
[TD][/TD]
[TD]Random Location Lookup
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AS104[/TD]
[TD][/TD]
[TD]=INDEX(Table1[Locn],RANDBETWEEN(1,COUNTA(Table1[Locn])),1)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]AS113[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]CR215[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]DP102[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Kaper]

With formulas only, I think it will be rather tough. Of course, one could write dedicated UDF (User Defined Function in VBA) either to return whole array of results at once or to draw random value except these which were drawn before.

But Easy and reliable solution with helper column filled with RAND function is really easy and reasonably elegant solution in my opinion.

Anyway, if you are interested in UDF, here is example one (copy this code to standard module):

Function random_draw(r_from As Range, r_except As Range) As Variant
Dim source_arr, exceptions_arr, i As Long, counter As Long
source_arr = r_from.Value
counter = UBound(source_arr)
exceptions_arr = r_except.Value
With New Collection
  For i = 1 To UBound(source_arr)
    .Add source_arr(i, 1), CStr(source_arr(i, 1))
  Next i
  On Error Resume Next
  For i = 1 To UBound(exceptions_arr)
    .Remove CStr(exceptions_arr(i, 1))
  Next i
  On Error GoTo 0
  random_draw = .Item(WorksheetFunction.RandBetween(1, .Count))
End With
End Function

and in your worksheet use it in cell C2 as:

=random_draw(A$2:A$5,C$1:C1)

and copy down