Randomize from a list

PEGWINN

unread,

Sep 16, 2007, 3:20:00 PM9/16/07

to

I am trying to create a random mix. The idea is to create as many unique six
digit combinations from a field of fifteen numbers as possible. I looked at
Rand and some other formulas and they just don't quite get it done. Thanks.
-- Phil

Ragdyer

unread,

Sep 16, 2007, 5:28:36 PM9/16/07

to

Don't quite follow.

What 15 numbers?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"PEGWINN" <PEG...@discussions.microsoft.com> wrote in message
news:358E268B-238F-445C...@microsoft.com...

PEGWINN

unread,

Sep 16, 2007, 5:54:00 PM9/16/07

to

I have a list of fifteen numbers in a row A1:A15 (using Excell 03)

I want to populate a "table"from those fifteen numbers in groups of six
until all possible combinations are created.

Out put would look something like this:

1 2 3 4 5 6
1 3 4 5 6 7

etc

HTH-- Thanks -- Phil

Ragdyer

unread,

Sep 16, 2007, 7:06:05 PM9/16/07

to

AGAIN ... What 15 numbers?

I only know of 10 digits, 0 to 9.
If you're referring to double or triple digits, how does that compute to a
*6 digit* number?
Or, are you not referring to a 6 *digit* number, but to 6 numbers of *any*
number of digits, as in a combination to a safe?

Need a better description for this old brain to start working.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"PEGWINN" <PEG...@discussions.microsoft.com> wrote in message

news:3ABF4737-7DFA-47EA...@microsoft.com...

PEGWINN

unread,

Sep 16, 2007, 8:36:01 PM9/16/07

to

I'm sorry.

I am building a spreadsheet using excel03 to consolidate my lottery club
systems into one all inclusive workbook.

One of the systems (which will be its' own sheet) is to select 15 numbers at
random from 1-54.

Then take those fifteen numbers and arrange them in combinations of six to
buy the tickets from. I can do it by hand (and do) but it is very time
consuming. So, I thought to automate it.

Thanks for your patience -- Phil

Gary''s Student

unread,

Sep 16, 2007, 9:14:01 PM9/16/07

to

checkout:
http://groups.google.com/group/microsoft.public.excel.programming/browse_frm/thread/d4adeddfb30f05b2/055d39b7d85d38cd?lnk=st&q=%22list+combinations%22+group%3A*excel&rnum=3#055d39b7d85d38cd
--
Gary''s Student - gsnu200745

Ragdyer

unread,

Sep 16, 2007, 9:32:53 PM9/16/07

to

Try Google, using
"lottery permutations"
as the subject.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"PEGWINN" <PEG...@discussions.microsoft.com> wrote in message

news:4F78C9BC-2BFC-4A2C...@microsoft.com...

> I'm sorry.
>
> I am building a spreadsheet using excel03 to consolidate my lottery club
> systems into one all inclusive workbook.
>
> One of the systems (which will be its' own sheet) is to select 15 numbers
at
> random from 1-54.
>
> Then take those fifteen numbers and arrange them in combinations of six to
> buy the tickets from. I can do it by hand (and do) but it is very time
> consuming. So, I thought to automate it.
>
> Thanks for your patience -- Phil
>
>
> "Ragdyer" wrote:
>
> > AGAIN ... What 15 numbers?
> >
> > I only know of 10 digits, 0 to 9.
> > If you're referring to double or triple digits, how does that compute to
a
> > *6 digit* number?
> > Or, are you not referring to a 6 *digit* number, but to 6 numbers of
*any*
> > number of digits, as in a combination to a safe?
> >
> > Need a better description for this old brain to start working.
> > --
> > Regards,
> >
> > RD
> >
>

PEGWINN

unread,

Sep 16, 2007, 11:26:02 PM9/16/07

to

Thanks RD and Gary's Student. I already did the google. I am not a math whiz
so most of it was over my head. I'm sure there is a way, but I am so rusty at
VBA that I will have to practice a bit.

Thanks again -- Phil

T. Valko

unread,

Sep 17, 2007, 12:25:20 AM9/17/07

to

See this:

http://tinyurl.com/6okbp

--
Biff
Microsoft Excel MVP

"PEGWINN" <PEG...@discussions.microsoft.com> wrote in message

news:F0E50679-2486-4C03...@microsoft.com...

Max

unread,

Sep 17, 2007, 6:44:01 AM9/17/07

to

> .. to select 15 numbers at random from 1-54.

a. To randomize the 15 numbers from 1-54,
you could try this quick setup in a new sheet:

List the numbers 1-54 in A1:A54

Put in B1: =RAND()
Copy down to B54

Put in C1: =INDEX(A:A,RANK(B1,B$1:B$54))
Copy down to C15. C1:C15 will return 15 random numbers from the set within
A1:A54. Press F9 to regenerate afresh.

If you want a full random scramble of the 54 numbers,
just copy C1 down all the way to C54.

> .. Then take those fifteen numbers
> and arrange them in combinations of six ...

b. To generate all possible combinations of sets of 6 from the 15 ..

Take away this ready-to-run sample from my archives
which contains an implementation of Myrna Larson's power subroutine:

http://savefile.com/files/518493
MyrnaLarson_Combination_Permutation.xls

For your posted needs,
ie Picking sets of 6 from 15 ..

In Sheet1,

Enter in A2: 6 (that's the pick you want)
then enter the 15 random numbers into A3:A17
[or just copy n paste special as values (or link)
the randomized 15 from C1:C15 in part (a) above into A3:A17]

Then *select* cell A1 (leave the letter in A1 as "C")
and click the button: ListPermutations

All 5,005 combinations [as COMBIN(15,6) = 5005]
will be generated in a new sheet to the left of Sheet1

Good luck <g>!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Confused

unread,

Mar 6, 2008, 5:00:00 PM3/6/08

to

I believe the function Rand will only generate values between 0 and 1.
Randbetween will give you random numbers from 0 to whatever. I couldn't get
your example to work. I will try randbetween with some other combinations.

David Biddulph

unread,

Mar 6, 2008, 5:17:31 PM3/6/08

to

In what way didn't the suggestion work? [... and don't you think that
someone would have spotted a problem some time between last September and
now?]
What results did you get in column C? Have you read again the suggestion
made, and checked what you did differently? Did you try to retype the
formulae rather than copying them from the group to your spreadsheet? Why
is it a problem that RAND() returns values between 0 and 1? What is to stop
us ranking those and using them to index into the range of numbers as in the
formula quoted?
--
David Biddulph

"Confused" <Conf...@discussions.microsoft.com> wrote in message
news:5346B913-2DAD-4474...@microsoft.com...

nancy

unread,

Aug 4, 2009, 6:59:02 AM8/4/09

to

i hav two worksheets in a workbook.sheet3 s the database..which s fr the
admins referal(not viewable by others).which contains the ID and password of
the employees.sheet1 looks lik the gn below:

ID NO. REMARKS SIGNATURE VERIFY

the employee types his respective password in the signature col of
sheet1.using =exact() fn. it cross checks if password s true fr the gn id.if
password-=true,verify=true.else false.

now the problem i am facing s dat..i am not able to enter the ID.s in any
random order in sheet1..and cross check the respective passwords...
So wat i need the sheet to look like is:

ID NO. REMARKS SIGNATURE VERIFY
1 good abc true
4
2
7
n so on....

can somone plz help me out of dis prob...
thanx fr ur help
nancy.