#1   Report Post  
Old November 3rd 08, 09:15 PM posted to microsoft.public.excel.misc
CRS CRS is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 13
Default 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.

  #2   Report Post  
Old November 3rd 08, 09:32 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2008
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.

  #3   Report Post  
Old November 3rd 08, 09:38 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 1,805
Default 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.

  #4   Report Post  
Old November 3rd 08, 09:45 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 29
Default 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.

  #5   Report Post  
Old November 3rd 08, 09:46 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2008
Posts: 1,358
Default 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.



  #6   Report Post  
Old November 3rd 08, 10:10 PM posted to microsoft.public.excel.misc
CRS CRS is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 13
Default 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.

  #7   Report Post  
Old November 3rd 08, 10:14 PM posted to microsoft.public.excel.misc
CRS CRS is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 13
Default 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.

  #8   Report Post  
Old November 3rd 08, 10:17 PM posted to microsoft.public.excel.misc
CRS CRS is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 13
Default 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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How would i make a football pool with 100 squares? need help Charts and Charting in Excel 1 September 5th 06 06:20 PM
chi squares Rozie Excel Discussion (Misc queries) 1 June 29th 06 09:52 PM
Football pool pats2306 Excel Discussion (Misc queries) 0 April 6th 06 10:35 PM
Football Pools Donald Excel Discussion (Misc queries) 0 December 14th 05 09:35 AM
removing squares and lines in squares that really should be paragr finnadat Excel Discussion (Misc queries) 5 February 10th 05 11:12 PM


All times are GMT +1. The time now is 02:58 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017