pick random names from a list

Question

I am trying to use a formula that it will allow me to pick 183 names randomly from a list of 355 names. My excel sheet will look something like this:

Names  Random.Names

Paty     
Oscar
John
Anna
Jane
Carlos
Maria
Jennifer
Susan
Kayla

On my actual sheet I have more names but this is just an example. I used the following formula but I have a few cells that show #REF after it randomizes.

=IF(ROWS($1:1)>$E$2,"",INDEX($A$8:$A$355,RANDBETWEEN(1,354)))

Please let me know if you have a better formula or if you know what I am doing wrong.

Would you like to guarantee 183 *different* names? Or just 183 *randomly chosen* names with replacement? — ERT, Jul 12 '17 at 14:38
Just a word of warning, If you do not provide feedback, by selecting correct answers, people will stop answering your questions. Please mark correct answers by clicking on the check mark by the answer that you used. — Scott Craner, Jul 12 '17 at 14:44
If your list is 355 names you are not allowing your formula to select anything past row 190 by using 1,182. — Scott Craner, Jul 12 '17 at 14:55

score 0 · Answer 1 · answered Jul 12 '17 at 14:37

0

That is because INDEX is relative, so row 8 is 1 and row 355 is 355-8+1 = 348. Change the RANDBETWEEN to 1,348

Anything greater than the number of cells referenced will produce the error.

=IF(ROWS($1:1)>$E$2,"",INDEX($A$8:$A$355,RANDBETWEEN(1,348)))

Or you can reference the whole column and use 8,355:

=IF(ROWS($1:1)>$E$2,"",INDEX($A:$A,RANDBETWEEN(8,355)))

answered Jul 12 '17 at 14:37

Scott Craner

148,073
10
49
81

Hi Scott, is this going randomize 355 names or 183? – G.S Jul 12 '17 at 15:06
This will look at the 355 list of names and return a random pick from that list. If you want 183 names from that list you will copy this formula down 183 times. – Scott Craner Jul 12 '17 at 15:08

score 0 · Answer 2 · answered Jul 12 '17 at 14:38

0

You do not have 355 names between A8 and A355 only 355-8+1.

So fix the RANDBETWEEN()

answered Jul 12 '17 at 14:38

Gary's Student

95,722
10
59
99

Yes, you are right. I didn't notice that. Thank you! – G.S Jul 12 '17 at 15:08

score 0 · Answer 3 · answered Jul 12 '17 at 19:45

Following the logic of my previous anwser

You only have to open your VBA editor an paste the following code:

  'By Julio Jesus Luna Moreno
'jlqmoreno@gmail.com
Option Base 1
Public Function UNIQRAND(a As Variant, b As Variant) As Variant
 Application.Volatile
 Dim k%, p As Double, flag As Boolean, x() As Variant
    k = 1
  flag = False
  ReDim x(1)
   x(1) = Application.RandBetween(a, b)
  Do Until k = b - a + 1

   Do While flag = False
   Randomize
    p = Application.RandBetween(a, b)
     'Debug.Assert p = 2
    resultado = Application.Match(p, x, False)
     If IsError(resultado) Then
      k = k + 1
      ReDim Preserve x(k)
      x(k) = p
       flag = True
      Else
       flag = False
      End If
   Loop
   flag = False
  Loop
  UNIQRAND = x
End Function

This function will do the trick

Public Function RANDNAMES(Rango As Range, HowMany As Integer) As Variant
 Dim n, p(), x(), i As Variant
  n = Rango.Rows.Count
   If n < HowMany Then
    MsgBox "Number of pairs must be less than number of total elements"
     Exit Function
   End If
   ReDim x(HowMany)
   ReDim p(n)
     p = UNIQRAND(1, n)
   For i = 1 To HowMany Step 1
    x(i) = Application.Index(Rango, p(i))
      Next i
       Debug.Print HowMany
    RANDNAMES = Application.Transpose(x)

End Function

pick random names from a list

3 Answers3