Fill random words

  • Thread starter Thread starter Hallrar
  • Start date Start date
H

Hallrar

I have a list of 100 unique words from the area A1:A100.
Then I want EXCEL to randomly fill the area B1:F8 with some of the words in
A1:A100.

Do you know how I could do that?

Hallvard
 
If you stick a helper column in before column A with the numbers 1 to
100 in it and use:

=VLOOKUP(RANDBETWEEN(1,100),$A$1:$B$100,2,FALSE)

Should do the trick
 
One way to play around with .. if duplicates is not an issue:

Put in B1: =OFFSET($A$1,randbetween(0,99),)
Copy across to F1, then fill down to F8

Press F9 to regenerate

Note: Randbetween() requires the Analysis Toolpak to work
(Tools > Add-Ins > Check "Analysis Toolpak" > OK)
 
Hallrar wrote...
I have a list of 100 unique words from the area A1:A100. Then I
want EXCEL to randomly fill the area B1:F8 with some of the
words in A1:A100.
Click to expand...

No one else has addressed nonduplicates yet. The simplest way is to us
a 100-row by 1-column range somewhere else with each cell containin
=RAND(), then use it along with the LARGE or SMALL function to pic
distinct entries from your list of words. If X1:X100 contained thi
array of random numbers, try

B1 [*array* formula]:
=INDEX($A$1:$A$100,MATCH(LARGE($X$1:$X$100,(ROW()-1)*5
+COLUMN()-1),$X$1:$X$100,0))

Select B1 and fill down into B2:B8, then select B1:B8 and fill righ
into C1:F8.

There's also a way to do this using just the B1:F8 result range.

B1:
=INDEX(A1:A100,INT(1+100*RAND()))

C1 [*array* formula]:
=INDEX($A$1:$A$100,LARGE(IF(COUNTIF($B1:B1,$A$1:$A$100)=0,
ROW($A$1:$A$100)),INT(1+(100-COUNTA($B1:B1))*RAND())))

Select C1 and fill right into D1:F1.

B2 [*array* formula]:
=INDEX($A$1:$A$100,LARGE(IF(COUNTIF($B$1:$F1,$A$1:$A$100)=0,
ROW($A$1:$A$100)),INT(1+(100-COUNTA($B$1:$F1))*RAND())))

C2 [*array* formula]:
=INDEX($A$1:$A$100,LARGE(IF(COUNTIF($B$1:$F1,$A$1:$A$100)
+COUNTIF($B2:B2,$A$1:$A$100)=0,ROW($A$1:$A$100)),
INT(1+(100-COUNTA($B$1:$F1,$B2:B2))*RAND())))

Select C2 and fill right into D2:F2. Then select B2:F2 and fill dow
into B3:F8
 
Great, J.E.!

My new personal favourite is a restricted & enhanced (why
calculate 100 random numbers if only 8 are necessary?)
version of your function which is used as follows:

With the UniqRandInt function installed (see below),
select B1:F8 and array-enter
(CTRL-SHIFT-ENTER or CMD-RETURN):

=INDEX(A1:A100,UNIQRANDINT(100))

Regards,
sulprobil

------ snip ---------

'*********************************************************
'Purpose: produce n unique random ints within 1..m, m >= n
'Inputs: mRange - highest possible random number in 1..m
' Implicitly: Length of Application.Caller Range
' (count of requested random numbers)
'Returns: array of unique random integers
'*********************************************************
Public Function UniqRandInt(ByVal mRange As Long) As
Variant
'returns n unique random ints within 1..m >= n
'Orig: J.E. McGimpsey
http://www.mcgimpsey.com/excel/randint.html
'Changed by: sulprobil http://Reverse
("moc.liborplus.www")
Dim vArr As Variant
Dim vResult As Variant
Dim nCount As Long
Dim nTemp As Long
Dim nRand As Long
Dim i As Long
Dim j As Long

Application.Volatile
If TypeName(Application.Caller) <> "Range" Then Exit
Function
With Application.Caller
ReDim vResult(1 To .Rows.Count, 1
To .Columns.Count)
nCount = .Count
If nCount > mRange Then
RandInt = CVErr(xlErrNum)
Exit Function
ElseIf nCount = 1 Then
UniqRandInt = CLng((mRange - 1) * Rnd() + 1)
Exit Function
End If
End With
ReDim vArr(1 To mRange)
For i = 1 To mRange
vArr(i) = i
Next i
nCount = 1
For i = 1 To UBound(vResult, 1)
For j = 1 To UBound(vResult, 2)
nRand = Int(((mRange - nCount + 1) * Rnd) + 1)
vResult(i, j) = vArr(nRand)
vArr(nRand) = vArr(mRange - nCount + 1)
nCount = nCount + 1
Next j
Next i
UniqRandInt = vResult
End Function


-------- snip ---------
 
JE McGimpsey said:
Well, not unless you count my post...

...but it wasn't a worksheet function solution.
Click to expand...

Sorry about that. I didn't follow your url. I've looked it over now. I'd
have done it differently (adding an optional 3rd arg to ignore
Application.Caller and return an array, so it could be called from Sub
procedures), but yours works as stated.
 
Harlan Grove said:
I'd
have done it differently (adding an optional 3rd arg to ignore
Application.Caller and return an array, so it could be called from Sub
procedures), but yours works as stated.
Click to expand...


I like that. I'm thinking two optional arguments, one for each index in
a two-dimensional array, though.
 
Back
Top