index random questions

i am sort of new at this and dealing with functions. i have a question about
indexing. once i have randomly generated a name or number from a list i
previously set up (and i got the random name by
using=index(a73:a173,rand()*99+1) how do i now generate another name but
making sure that the two names do not come up twice. is there an easy formula
for this?
i use it for numbers also, i guess there's no difference. in other words i
dont wont the names/numbers to come up twice when generating.
thanks.

What you're really looking for is a *random order* of pre-determined data,
names or numbers.

The simplest approach for something like this is to insert a column along
side your existing column of data, and enter the Rand() function in this
column.
Then sort the 2 columns, with the Rand() function as the sort key, and
you'll have your "Random Order", of course, without any duplication.

There are slightly more intricate procedures using a formula, where you can
display a new random order with every click of the <F9> key, where sorting
is not necessary.

Post back if you might be interested in this.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"mcmil" <mcmil@discussions.microsoft.com> wrote in message
news:687FBD34-0BA5-44FB-B6CA-A02F5A757D7D@microsoft.com...
> i am sort of new at this and dealing with functions. i have a question
about
> indexing. once i have randomly generated a name or number from a list i
> previously set up (and i got the random name by
> using=index(a73:a173,rand()*99+1) how do i now generate another name but
> making sure that the two names do not come up twice. is there an easy
formula
> for this?
> i use it for numbers also, i guess there's no difference. in other words i
> dont wont the names/numbers to come up twice when generating.
> thanks.

yes thats what i mostly use to generate the numbers i hit (f9). i'm still not
to clear but i'll try the ask the question i'm thinking of. lets say that you
have a 52 card deck (which is mostly what i'm generating) and with each
number that comes up after generating i have a corresponding text that comes
up with (ex. if i generate a 1 out of rand()*51+1 then the text will come up
as 2 of clubs etc..). so i guess what i'm trying to ask is there a formula
for making sure that your five card hand is not dupliacted? does this make
any sense? i appreciate youyr feedback.

"Ragdyer" wrote:

> What you're really looking for is a *random order* of pre-determined data,
> names or numbers.
>
> The simplest approach for something like this is to insert a column along
> side your existing column of data, and enter the Rand() function in this
> column.
> Then sort the 2 columns, with the Rand() function as the sort key, and
> you'll have your "Random Order", of course, without any duplication.
>
> There are slightly more intricate procedures using a formula, where you can
> display a new random order with every click of the <F9> key, where sorting
> is not necessary.
>
> Post back if you might be interested in this.
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "mcmil" <mcmil@discussions.microsoft.com> wrote in message
> news:687FBD34-0BA5-44FB-B6CA-A02F5A757D7D@microsoft.com...
> > i am sort of new at this and dealing with functions. i have a question
> about
> > indexing. once i have randomly generated a name or number from a list i
> > previously set up (and i got the random name by
> > using=index(a73:a173,rand()*99+1) how do i now generate another name but
> > making sure that the two names do not come up twice. is there an easy
> formula
> > for this?
> > i use it for numbers also, i guess there's no difference. in other words i
> > dont wont the names/numbers to come up twice when generating.
> > thanks.
>
>

You can put your list of playing cards in an "out-of-the-way" portion of
your sheet, say Y1:Y52.
In Z1:Z52 enter the rand function:

=RAND()

Then, enter this formula anywhere you wish, and copy it down 5 cells.
For me, it would be 7 cells, 'cause I'm partial to 7 card stud.<g>

=INDEX($Y$1:$Y$52,RANK(Z1,$Z$1:$Z$52))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mcmil" <mcmil@discussions.microsoft.com> wrote in message
news:94892BD4-F55D-4EA2-A216-7FF36F6238B2@microsoft.com...
yes thats what i mostly use to generate the numbers i hit (f9). i'm still
not
to clear but i'll try the ask the question i'm thinking of. lets say that
you
have a 52 card deck (which is mostly what i'm generating) and with each
number that comes up after generating i have a corresponding text that comes
up with (ex. if i generate a 1 out of rand()*51+1 then the text will come up
as 2 of clubs etc..). so i guess what i'm trying to ask is there a formula
for making sure that your five card hand is not dupliacted? does this make
any sense? i appreciate youyr feedback.

"Ragdyer" wrote:

> What you're really looking for is a *random order* of pre-determined data,
> names or numbers.
>
> The simplest approach for something like this is to insert a column along
> side your existing column of data, and enter the Rand() function in this
> column.
> Then sort the 2 columns, with the Rand() function as the sort key, and
> you'll have your "Random Order", of course, without any duplication.
>
> There are slightly more intricate procedures using a formula, where you
can
> display a new random order with every click of the <F9> key, where sorting
> is not necessary.
>
> Post back if you might be interested in this.
> --
> Regards,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
>
> "mcmil" <mcmil@discussions.microsoft.com> wrote in message
> news:687FBD34-0BA5-44FB-B6CA-A02F5A757D7D@microsoft.com...
> > i am sort of new at this and dealing with functions. i have a question
> about
> > indexing. once i have randomly generated a name or number from a list i
> > previously set up (and i got the random name by
> > using=index(a73:a173,rand()*99+1) how do i now generate another name but
> > making sure that the two names do not come up twice. is there an easy
> formula
> > for this?
> > i use it for numbers also, i guess there's no difference. in other words
i
> > dont wont the names/numbers to come up twice when generating.
> > thanks.
>
>

Hello,

I suggest to use my function UniqRandInt().

Then you might enter (as array formula - enter with CTRL + SHIFT + ENTER):
=index(a73:a173,uniqrandint(100))

HTH,
Bernd