Forum Discussion
=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/
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.
19 Replies
- Why are there no move and copy buttons in the windows 11 toolbar ? ? ? The toolbar in the window 11 is useless !!!
- 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?The problem is much the same only you have to finish by picking a block of 7 values
= INDEX(RANK(random, random),{1;2;3;4;5;6;7})or as I would prefer using 365 beta
= LET( k, SEQUENCE(52), rand, RANDARRAY(52), ordered, SORTBY(k,rand), TAKE(ordered,7) )
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)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))
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.
How many random numbers do you want?
- 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.
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.
- 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 🙂
- Thank you, will check it out now.
Just noticed that i had
x = Int(Rnd * 50 + 1)
instead of
x = Int(Rnd * 52 + 1)
Attached please find the adapted file for randbetween 1-52.
