SOLVED

# =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

Occasional Contributor

# =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

Hi,

I have been struggling with this for some time, I have office 2019, and I want to use that formula as stated in Subject to get a row of random numbers everytime, but the number may have no duplicates, any help would be higly appreciated/

17 Replies
best response confirmed by SpartanBoy02 (Occasional Contributor)
Solution

# Re: =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

In the attached file in cell A1 you can enter the number of random numbers which will be displayed in range A3:A54. Click the button in cell D2 to start the macro. Maybe this is what you want to do.

# Re: =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

How many random numbers do you want?

# Re: =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

Thank you, will check it out now.

1-52

# Re: =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

x = Int(Rnd * 50 + 1)

x = Int(Rnd * 52 + 1)

# Re: =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

You can use this simple approach if you are using excel 365
INDEX(UNIQUE(RANDARRAY(n^2, 1, min, max, FALSE)), SEQUENCE(n))
=INDEX(UNIQUE(RANDARRAY(10^2,1,1,20,TRUE)),SEQUENCE(10))

in fact, iCan easily use this alone without duplicates

=UNIQUE(RANDARRAY(20,1,2,20,1))

# Re: =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

A low-tech approach that works in all versions of Excel:

Enter the numbers 1 and 2 in A1 and A2.

Select A1:A2, then fill down to A52.

In B1, enter the formula =RAND()

Fill down to B52.

Click in B1, then sort ascending.

The numbers in A1:A52 are now in random order.

# Re: =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

You're right! i was thinking he is using office 365,
By the way, i also like your method! absolutely i love your own method

I learnt something new today courtesy of you

# Re: =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

@Donald_Genes_

Thank you for your response, I tried both and the unique and sequence formula does not pick up. I am in the latest version that is why I do not understand why some formulas are working and others not. Please see attached picture for my version.

# Re: =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

Thank you for your reply, If i use RAND then i get values of 0.1555 or 0.8555, I can use =INT to round it to closest number, But this way I will still sit with duplicates, everytime i click on anything it must still recalculate and give me random number, no 0.1888 numbers and then also no duplicates.

I used this one now, it stil give me duplicates though.
=INT(RAND()*52)

# Re: =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

This partially works, how can I make it use number from 1-52 and still have 52 cells, like all numbers must be shown everytime and when I click it it must give me random values but still 52 cells. Every number must be shown from 1-52 everytime I recalculate?

# Re: =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

If you enter 52 in cell A1 it will give you 52 different values for every recalculation.

# Re: =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

I felt sorry for Excel 2019 users in that they missed the cut and didn't get the new dynamic arrays.  Perhaps there should be a trade-in offer to move to Excel 2021 or 365?

``````= SORTBY(
SEQUENCE(52),
RANDARRAY(52)
)``````

is neat and self contained.  Prior to that, a helper range is needed, so that the random numbers can be ranked without the values changing throughout the calculation

``````"helper column, 'random'"
= RAND()

"Formula column, array or implicit intersection"
= RANK(random, random)``````

# Re: =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

365 with some fancy formatting!

# Re: =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

Thank you so much, I was very tired last time i tried it, did not even see it. Thank you so much for the help, I appreciate it, and thanks to everyone that was willing to help. I will defnitely come back if I have blonde moments again

# Re: =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

Hi guys, trying to follow this an i like the screen above. Kind of what I want to achieve.
So, wanna draw 7 cards from deck of 52. How do i ensure that same card is not drawn?

# Re: =RANDBETWEEN NO DUPLICATES BETWEEN 1-52

Why are there no move and copy buttons in the windows 11 toolbar ? ? ? The toolbar in the window 11 is useless !!!