football squares
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. |
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. |
football squares
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. |
football squares
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. |
football squares
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. |
football squares
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. |
football squares
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. |
football squares
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. |
All times are GMT +1. The time now is 02:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com