Help using RANDBETWEEN

[retghy]

Hello all.

I'm using the RANDBETWEEN(1,15) excel function to fill a range of cells with random numbers between 1 and 15, the problem is that each cell cannot be the same number than any other in that range, how exactly can i accomplish this?

I attached a file for a better example, if you hit F9 you can see that sometimes at least 2 cells share the same number...but I need it to be different on each one, and still be random.

Any tip or help would be very apreciated.

Thank you in advance.

[JBeaucaire]

This is one way to insure duplicates don't happen.

[DonkeyOte]

If you run a search on the board of Unique Random Numbers I think you will find a few posts.

One nice little approach that doesn't require too much thinking is to use helpers, using your sample file for example:

F4: =ROWS(F$4:F4)
copied to F18 ... thereby generating 1 to 15

G4: =RAND()
copied to G18

You can then retrieve 1-15 randomly based on the RAND value in G, say pulling associated value based on RAND LARGE 1 to LARGE 9

D4: =INDEX($F$4:$F$18,MATCH(LARGE($G$4:$G$18,ROWS(D$4:D4)),$G$4:$G$18,0))
copied down to D12


EDIT: above is identical approach to JB... quick this eve. (?) JB

[daddylonglegs]

Without using a helper column......you could use this formula in D4 copied down

=SMALL(IF(COUNTIF(D$3:D3,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}),"", {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}),INT(RAND()*(15-ROWS(D$4:D4)+1))+1)

[daddylonglegs]

...and for a helper column approach....you can use this formula in C4 copied to C18

=RAND()

and then in D4 copied down

=RANK(C4,C$4:C$18)

[retghy]

Thank you for your quick answers, I'm on my way to work, can't wait to try your solutions. I'll let you know how it goes.

Last nigth I was trying using an IF statement (if number is equal than cell above generate another) but no luck, I see you got a different approach.

Thanks again.

[retghy]

Without using a helper column......you could use this formula in D4 copied down

=SMALL(IF(COUNTIF(D$3:D3,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}),"", {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}),INT(RAND()*(15-ROWS(D$4:D4)+1))+1)

I couldn't make it work using that formula, maybe because my office suite is in spanish and I picked the wrong function.

The helpper method worked great, thank you!

With all this a was trying to make 50 columns and none can be equal and the thing with it is that when you apply the same formulas to another colum you get the same results of course.

Do I have to create another helper page for every column I need?

[DonkeyOte]

I couldn't make it work using that formula, maybe because my office suite is in spanish and I picked the wrong function.

I believe in Spanish this:

Please Login or Register  to view this content.

would perhaps become the below in terms of delimiter syntax:

Please Login or Register  to view this content.

or if functions are also in Spanish perhaps:

Please Login or Register  to view this content.

[martindwilson]

muy bueno ,Don Quixote lol

[DonkeyOte]

de nada ...

[retghy]

I believe in Spanish this:

Please Login or Register  to view this content.

would perhaps become the below in terms of delimiter syntax:

Please Login or Register  to view this content.

or if functions are also in Spanish perhaps:

Please Login or Register  to view this content.

Muy agradecido por su ayuda.

It works without the helpers, but sadly you still get duplicates in the column.

[daddylonglegs]

Muy agradecido por su ayuda.

It works without the helpers, but sadly you still get duplicates in the column.

You shouldn't get duplicates. Here's an example using your original......

[retghy]

You shouldn't get duplicates. Here's an example using your original......

In your example I don't get any duplicate your right, maybe it was something with my function.

Thank you all for the tips and help, you've been very supportive.

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in the posts to leave Reputation Feedback, it is appreciated)