PDA

View Full Version : Solved: Help on selecting unique random words from a list.



sagotianitin
05-24-2013, 01:33 AM
Hello Everyone!

Please anybody can help me on below query:
I need to select unique random word from a given list of words.

Example:

Prerequsite: Assume, I have 100 words list in coloums A1:A100.

Like:
asf
eft
jhg & so on...

Now, enter the number of unique words needed.
Suppose - We entered 10 then, I should get 10 different/unique random words from the list of 100 words.

Kindly help on this! :)

Thanks,
Nitin Jain

mancubus
05-24-2013, 05:22 AM
hi.

try this:


Sub Random_Selection_From_List()

Dim fromRng As New Collection
Dim cll As Range
Dim toRand() As String
Dim i As Long, j As Long, num As Long

For Each cll In Range("A1:A100")
fromRng.Add cll.Value
Next

num = Application.InputBox("Enter the Number of Elements", "RANDOM SELECTION", 10)

ReDim toRand(1 To num)

For i = LBound(toRand) To UBound(toRand)
j = Int(Rnd * fromRng.Count) + 1
toRand(i) = fromRng(j)
fromRng.Remove j
Next

'do whatever you want with the selected values
'my example: write array elements to range starting at J1 in the same worksheet
Range("J1").Resize(UBound(toRand)) = Application.Transpose(toRand)

End Sub

sagotianitin
05-24-2013, 11:50 PM
Thanks mancubus!

mancubus
05-25-2013, 12:29 AM
You are welcome.