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.

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.

Requirement is not clear.

What is the size of the string? How many strings you want?

"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.

Since CRS wrote "football squares" as the subject, I'm betting he wants 0
through 9 going across the page, and 0 - 9 in column A as well. :-)
Of course, gambling is illegal in most states, so I'm sure this "sheet" is
just for fun.
;-)
SpenCer

"Sheeloo" wrote:

Requirement is not clear.

What is the size of the string? How many strings you want?

"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.

Correction on my first solution, the numbers 0-9 would be entered in cells
A1:A10, then you could use CONCATENATE, on all the cells in C1:C10
=CONCATENATE(C1,C2,C3,C4,C5,C6,C7,C8,C9,C10)
--
** John C **

"John C" wrote:

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.

If you are familiar with office football pools, based on the score, you will
probably know what I need without further explanation. But, just in case,
here goes:

Consider a grid, rix 11r x 11c (say a1:k11). People choose individual cells
in b2:k11. a2:a11 will need to be populated randomly with a single,
non-repeating integer with a value from 0 to 9. Likewise for b1:k1. The
intersection of the row and column would represent the final digits of the
scores of the 2 teams at a given time (say the end of a quarter, half or
final). Cell A1 has no influence in this example.

Sorry I was not more clear in my initial post.

"Sheeloo" wrote:

Requirement is not clear.

What is the size of the string? How many strings you want?

"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.

Thank you, John. I was able to utilize the first example for my needs. I
appreciate the inclusion of the other examples as well. I will use them as a
study basis for my futures needs. I have already benefitted from your input
for other posters as well on issues that I have been curious about. Thank
you for sharing your knowledge

"John C" wrote:

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.

Correct SpenCer. Friendly water-cooler/coffee break competition amongst
co-workers.

"SpenCer" wrote:

Since CRS wrote "football squares" as the subject, I'm betting he wants 0
through 9 going across the page, and 0 - 9 in column A as well. :-)
Of course, gambling is illegal in most states, so I'm sure this "sheet" is
just for fun.
;-)
SpenCer

"Sheeloo" wrote:

Requirement is not clear.

What is the size of the string? How many strings you want?

"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.