Remember Me?

#1
November 3rd 08, 09:15 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Nov 2008 Posts: 13
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
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
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
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
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
November 3rd 08, 09:45 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Oct 2006 Posts: 29
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
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
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
November 3rd 08, 10:10 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Nov 2008 Posts: 13
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
November 3rd 08, 10:14 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Nov 2008 Posts: 13
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

"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
November 3rd 08, 10:17 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Nov 2008 Posts: 13
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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post need help Charts and Charting in Excel 1 September 5th 06 06:20 PM Rozie Excel Discussion (Misc queries) 1 June 29th 06 09:52 PM pats2306 Excel Discussion (Misc queries) 0 April 6th 06 10:35 PM Donald Excel Discussion (Misc queries) 0 December 14th 05 09:35 AM finnadat Excel Discussion (Misc queries) 5 February 10th 05 11:12 PM

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