Feb 06 2022 11:27 AM
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/
Feb 06 2022 12:26 PM
SolutionIn 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.
Feb 06 2022 12:28 PM
How many random numbers do you want?
Feb 06 2022 01:08 PM
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.
Feb 06 2022 01:15 PM - edited Feb 06 2022 01:23 PM
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))
Feb 06 2022 02:09 PM
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.
Feb 06 2022 02:30 PM
Feb 06 2022 07:42 PM
@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.
Feb 06 2022 07:51 PM
Feb 06 2022 07:55 PM
Feb 07 2022 02:48 AM
If you enter 52 in cell A1 it will give you 52 different values for every recalculation.
Feb 07 2022 04:10 AM
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)
Feb 07 2022 08:49 AM
Feb 08 2022 07:16 AM
May 20 2022 04:27 AM
May 20 2022 04:45 AM
May 21 2022 01:23 AM
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)
)
May 21 2022 05:36 PM
Feb 06 2022 12:26 PM
SolutionIn 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.