Home 
Search 
Today's Posts 
#1




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




football squares
Couple ways:
in cells A1:A10, enter numbers 110 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 09, with no repitition. I have tried using RANDOM and RANDOMBETWEEN, but am not sure how to avoid repition. 
#3




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 09, with no repitition. I have tried using RANDOM and RANDOMBETWEEN, but am not sure how to avoid repition. 
#4




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 09, with no repitition. I have tried using RANDOM and RANDOMBETWEEN, but am not sure how to avoid repition. 
#5




football squares
Correction on my first solution, the numbers 09 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 110 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 09, with no repitition. I have tried using RANDOM and RANDOMBETWEEN, but am not sure how to avoid repition. 
#6




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, nonrepeating 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 09, with no repitition. I have tried using RANDOM and RANDOMBETWEEN, but am not sure how to avoid repition. 
#7




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 110 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 09, with no repitition. I have tried using RANDOM and RANDOMBETWEEN, but am not sure how to avoid repition. 
#8




football squares
Correct SpenCer. Friendly watercooler/coffee break competition amongst
coworkers. "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 09, with no repitition. I have tried using RANDOM and RANDOMBETWEEN, but am not sure how to avoid repition. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
How would i make a football pool with 100 squares?  Charts and Charting in Excel  
chi squares  Excel Discussion (Misc queries)  
Football pool  Excel Discussion (Misc queries)  
Football Pools  Excel Discussion (Misc queries)  
removing squares and lines in squares that really should be paragr  Excel Discussion (Misc queries) 