Hello,

I need to randomly populate a 10x10 array of cells with 100 names - 5
people listed 20 times each {Tom, Sue, Ray, Lin, Bob}.

For example, in the range, C3:L12, I need Tom listed exactly 20 times,
Sue listed 20 times, etc.

A VBA solution is the preferred method!

Thanks,

Kevin

One way:

Public Sub RandomNames()
Dim vArr As Variant
Dim vResult As Variant
Dim vNames As Variant
Dim i As Long
Dim j As Long
Dim temp As String
Dim nRand As Long

vNames = Array("Tom", "Sue", "Ray", "Lin", "Bob")

ReDim vArr(1 To 100)
For i = 0 To 4
For j = 1 To 20
vArr(i * 20 + j) = vNames(i)
Next j
Next i

For i = 100 To 2 Step -1
nRand = Int(Rnd() * 100) + 1
temp = vArr(i)
vArr(i) = vArr(nRand)
vArr(nRand) = temp
Next i

ReDim vResult(1 To 10, 1 To 10)
For i = 1 To 10
For j = 1 To 10
vResult(i, j) = vArr((i - 1) * 10 + j)
Next j
Next i

Range("C3:L12").Value = vResult
End Sub





In article ,
(Kevin Lyons) wrote:

Hello,

I need to randomly populate a 10x10 array of cells with 100 names - 5
people listed 20 times each {Tom, Sue, Ray, Lin, Bob}.

For example, in the range, C3:L12, I need Tom listed exactly 20 times,
Sue listed 20 times, etc.

A VBA solution is the preferred method!

Thanks,

Kevin

Kevin,

Probably a lot slower than most solutions, since if it doesn't find a match
it'll keep generating random numbers until it gets a hit. Chances are with
just 5 names it'll get lucky pretty quick - with 1000 names maybe not so
lucky and not so fast.


Sub test()
Dim arr(10 - 1, 10 - 1) As String, i As Long, j As Long, lngRnd As Long
Dim arrName As Variant, arrCount As Variant

arrName = Array("Tom", "Sue", "Ray", "Lin", "Bob")
arrCount = Array(20, 20, 20, 20, 20)

Randomize
For i = 0 To 10 - 1
For j = 0 To 10 - 1
Do
lngRnd = Int(5 * Rnd)
Loop While arrCount(lngRnd) = 0
arrCount(lngRnd) = arrCount(lngRnd) - 1
arr(i, j) = arrName(lngRnd)
Next
Next

Range("C3:L12").Value = arr
End Sub



--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Kevin Lyons" wrote in message
m...
Hello,

I need to randomly populate a 10x10 array of cells with 100 names - 5
people listed 20 times each {Tom, Sue, Ray, Lin, Bob}.

For example, in the range, C3:L12, I need Tom listed exactly 20 times,
Sue listed 20 times, etc.

A VBA solution is the preferred method!

Thanks,

Kevin

Got a bit of a mix. Mostly macro, but I use the randbetween formula to get
a random selection. And I populate column D first
This list is then picked up in the area E2 to N11

the macro .....................

Sub populate5twenty()
Dim counter
Dim tom
Dim sue
Dim ray
Dim lin
Dim bob

' Macro recorded 8/02/2004 by Bill

Start:

Calculate 'to randomly select the next
name
If counter = 100 Then Exit Sub 'after 100 names pasted stop

If Range("b1") = 1 And tom = 20 Then GoTo Start 'range b1 has formula
=randbetween(1,5)
If Range("b1") = 2 And sue = 20 Then GoTo Start 'if we had twenty try again
If Range("b1") = 3 And ray = 20 Then GoTo Start
If Range("b1") = 4 And lin = 20 Then GoTo Start
If Range("b1") = 5 And bob = 20 Then GoTo Start


If Range("b1") = 1 Then tom = tom + 1 'add one to counter for tom
If Range("b1") = 2 Then sue = sue + 1
If Range("b1") = 3 Then ray = ray + 1
If Range("b1") = 4 Then lin = lin + 1
If Range("b1") = 5 Then bob = bob + 1

counter = counter + 1


Range("C1").Select 'range c1 has formula to convert
number 1 to 5 into names
Selection.Copy
Range("D1").Select
ActiveCell.Offset(counter, 0).Activate 'go down one row in column D
before pasting the next name

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False


GoTo Start
End Sub
"Kevin Lyons" wrote in message
m...
Hello,

I need to randomly populate a 10x10 array of cells with 100 names - 5
people listed 20 times each {Tom, Sue, Ray, Lin, Bob}.

For example, in the range, C3:L12, I need Tom listed exactly 20 times,
Sue listed 20 times, etc.

A VBA solution is the preferred method!

Thanks,

Kevin