how to create a Random list of names without duplicates

roony

New Member
Joined
Sep 19, 2006
Messages
1
hi,

i've tried searching, googling but cant find anything to get rid of the duplicates in my list.

bob
bill
john
jim

i keep getting duplicates everytime i try to randomise the list.

any urls or some code or something, wld be appreciated, i need this pretty quick, thanks
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
This was posted by SIXTHSENSE some time ago:

It randomises 8 names from a list.
First create a table of names:
Type sequence 1 - 20 in column F
Type names in column G

The macro will put random numbers in A1:A8
In B1 put this formula:
=VLOOKUP(A1,$F$1:$G$20,2,FALSE)

(ie the macro sets random number in column A and the formula looks up that number in your table)

Sub RandomNumbers() 'by SIXTHSENSE
Dim ranNum(8) As Integer
counter = 1
While counter <= 8
tmp = Int((20 * Rnd) + 1)
found = False
For j = 1 To counter
If tmp = ranNum(j) Then
found = True
Exit For
End If
Next j
If found = False Then
ranNum(counter) = tmp
counter = counter + 1
End If
Wend
For i = 1 To 8
Range("a" & i).Value = ranNum(i)
Next i
End Sub

regards
Derek
 

Goonitup

New Member
Joined
Sep 30, 2013
Messages
8
This was posted by SIXTHSENSE some time ago:

It randomises 8 names from a list.
First create a table of names:
Type sequence 1 - 20 in column F
Type names in column G

The macro will put random numbers in A1:A8
In B1 put this formula:
=VLOOKUP(A1,$F$1:$G$20,2,FALSE)

(ie the macro sets random number in column A and the formula looks up that number in your table)

Sub RandomNumbers() 'by SIXTHSENSE
Dim ranNum(8) As Integer
counter = 1
While counter <= 8
tmp = Int((20 * Rnd) + 1)
found = False
For j = 1 To counter
If tmp = ranNum(j) Then
found = True
Exit For
End If
Next j
If found = False Then
ranNum(counter) = tmp
counter = counter + 1
End If
Wend
For i = 1 To 8
Range("a" & i).Value = ranNum(i)
Next i
End Sub

regards
Derek
The formual above does not work to produce a name without a duplicate. I keep getting the duplicates..I want to know is there a way to select a name and place it in a bracket and not have it selected again.. I have tried random selection and table array and nothing works.. I can get the names to not repeat be whatever is in a1 will always be in the 1st or top bracket slot.. I want the names to ge into a random slot... is there a way to do this off of a list of names
 

Forum statistics

Threads
1,136,420
Messages
5,675,737
Members
419,585
Latest member
popsin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top