View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
John C[_2_] John C[_2_] is offline
external usenet poster
 
Posts: 1,358
Default football squares

Couple ways:
in cells A1:A10, enter numbers 1-10
in cells B1:B10, enter =RAND()
in cell C1: =INDEX($A$1:$A$10,RANK(B1,$B$1:$B$10))

or, a far more convoluted way :)
A2: =RANDBETWEEN(1,10)
A4: =RANDBETWEEN(1,9)
A6: =RANDBETWEEN(1,8)
....
A18: =RANDBETWEEN(1,2)
B1: ="0123456789"
B2: =MID($B1,A2,1)
B3: =SUBSTITUTE(B1,B2,"")
B4: =MID($B3,A4,1)
B5: =SUBSTITUTE(B3,B4,"")
B6: =MID($B5,A6,1)
B7: =SUBSTITUTE(B5,B6,"")
....
B18: =MID($B17,A18,1)
B19: =SUBSTITUTE(B17,B18,"")

C2: =CONCATENATE(B2,B4,B6,B8,B10,B12,B14,B16,B18,B19)

:O)

--
** John C **

"CRS" wrote:

I am in need of a formula or function to generate a random string containing
the integers 0-9, with no repitition. I have tried using RANDOM and
RANDOMBETWEEN, but am not sure how to avoid repition.