Random Numbers Not Equal to

Hi,

I have 8 cells that I want to contain a unique whole number from 1 to
8. I don't want any of the 8 numbers to repeat.

Any ideas?

Joe

Take a look here:

http://www.mcgimpsey.com/excel/udfs/randint.html



In article <oj2jt0hteu0hklibp79vs6t24fpsnffvm4@4ax.com>,
Joe Blow <JoeBlow@kokomo.com> wrote:

> I have 8 cells that I want to contain a unique whole number from 1 to
> 8. I don't want any of the 8 numbers to repeat.

One very limited way:

B1: 12345678
B2: =SUBSTITUTE(B1,A1,"")
A1: =MID(B1,RAND()*(LEN(B1)-1)+1,1)*1

Copy the the formula in A1 down to A8, and the one in B2
down to B8.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Hi,
>
>I have 8 cells that I want to contain a unique whole
number from 1 to
>8. I don't want any of the 8 numbers to repeat.
>
>Any ideas?
>
>Joe
>.
>

Joe

Visit John McGimsey's site for info on non-repeating RAND().

http://www.mcgimpsey.com/excel/udfs/randint.html


Gord Dibben Excel MVP

On Mon, 03 Jan 2005 13:12:07 -0500, Joe Blow <JoeBlow@kokomo.com> wrote:

>Hi,
>
>I have 8 cells that I want to contain a unique whole number from 1 to
>8. I don't want any of the 8 numbers to repeat.
>
>Any ideas?
>
>Joe

Cool, though this always results in 8 being the last selected. It seems to
work OK with this in A1;

=MID(B1,ROUNDUP(RAND()*(LEN(B1)),0),1)*1


"Jason Morin" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote in message
news:001c01c4f1c6$00c36300$a601280a@phx.gbl...
> One very limited way:
>
> B1: 12345678
> B2: =SUBSTITUTE(B1,A1,"")
> A1: =MID(B1,RAND()*(LEN(B1)-1)+1,1)*1
>
> Copy the the formula in A1 down to A8, and the one in B2
> down to B8.
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >Hi,
> >
> >I have 8 cells that I want to contain a unique whole
> number from 1 to
> >8. I don't want any of the 8 numbers to repeat.
> >
> >Any ideas?
> >
> >Joe
> >.
> >

You're right. Should be:

=MID(B1,RAND()*(LEN(B1))+1,1)*1

I rarely use RAND - just RANDBETWEEN.

Jason

>-----Original Message-----
>Cool, though this always results in 8 being the last
selected. It seems to
>work OK with this in A1;
>
>=MID(B1,ROUNDUP(RAND()*(LEN(B1)),0),1)*1
>
>
>"Jason Morin" <jasonjmorin@OPPOSITEOFCOLDmail.com> wrote
in message
>news:001c01c4f1c6$00c36300$a601280a@phx.gbl...
>> One very limited way:
>>
>> B1: 12345678
>> B2: =SUBSTITUTE(B1,A1,"")
>> A1: =MID(B1,RAND()*(LEN(B1)-1)+1,1)*1
>>
>> Copy the the formula in A1 down to A8, and the one in
B2
>> down to B8.
>>
>> HTH
>> Jason
>> Atlanta, GA
>>
>> >-----Original Message-----
>> >Hi,
>> >
>> >I have 8 cells that I want to contain a unique whole
>> number from 1 to
>> >8. I don't want any of the 8 numbers to repeat.
>> >
>> >Any ideas?
>> >
>> >Joe
>> >.
>> >
>
>
>.
>