Random Selection in Excel

[khawarameer]

Hello, I want to have an excel sheet which selects 32 names from given 60 names randomly and then arrange these 32 names into 8 groups of 4 each.

can any one help me?

Thanks

 

[MickG]

Hi, Try this:-
Your Data Column "A", Results start "D1".

[COLOR="Navy"]Sub[/COLOR] MG02Jun52
[COLOR="Green"][B]'32= 8 Coloumns of 4[/B][/COLOR]
[COLOR="Navy"]Dim[/COLOR] x [COLOR="Navy"]As[/COLOR] Range, RanRng [COLOR="Navy"]As[/COLOR] Range, z [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] J [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] real, oSt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]

 [COLOR="Navy"]Set[/COLOR] RanRng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
  ReDim oRes(1 To RanRng.Rows.Count)
    RanRng.Offset(, 1).ClearContents

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] x [COLOR="Navy"]In[/COLOR] RanRng.Offset(, 1)
  J = Int(Rnd() * RanRng.Rows.Count) + 1
    [COLOR="Navy"]Set[/COLOR] z = RanRng.Offset(, 1).Find(J, lookat:=xlWhole)
[COLOR="Navy"]While[/COLOR] Not z [COLOR="Navy"]Is[/COLOR] Nothing
     J = Int(Rnd() * RanRng.Rows.Count) + 1
       [COLOR="Navy"]Set[/COLOR] z = RanRng.Offset(, 1).Find(J, lookat:=xlWhole)
   Wend
x = J
oRes(x.Row) = J
[COLOR="Navy"]Next[/COLOR] x

ReDim real(1 To 4, 1 To 8)
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] Dn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
col = 1: Dn = 1
    [COLOR="Navy"]For[/COLOR] oSt = 1 To 32
            real(Dn, col) = Cells(oRes(oSt), 1)
                Dn = Dn + 1
        [COLOR="Navy"]If[/COLOR] oSt Mod 4 = 0 [COLOR="Navy"]Then[/COLOR] col = col + 1: Dn = 1
    [COLOR="Navy"]Next[/COLOR] oSt

Range("D1").Resize(4, 8).Value = real
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[khawarameer]

Thanks Mick.