how to genarate at A1:A18 random numbers from 1-36 withoutb dublicates.thanks !
how to genarate at A1:A18 random numbers from 1-36 withoutb dublicates.thanks !
I tend to use a shuffling technique for this:
1) Generate 36 random unique numbers. I usually use the RAND() function for this. =RAND() into B1 and copy down to B36.
2) Now "rank" the random numbers in either small to large or large to small using the RANK() function. =RANK($B$1:$B$36,B1) and copy/paste/fill down to A18.
Originally Posted by shg
great,but is there any formula which doing it direct?
Hi,
In A1:
=RANDBETWEEN(1,36)
In A2, array formula**:
=INDEX(MODE.MULT(IF(ISNA(MATCH(ROW($1:$36),A$1:A1,{0,0})),ROW($1:$36))),RANDBETWEEN(1,COUNT(1/ISNA(MATCH(ROW($1:$36),A$1:A1,0)))))
and copied down to A18.
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
thanks so much,i put the formula in the attached workbook,could you please check it cause there is something that i dont understand?
You haven't put the formulas in the cells I specified. I don't know why you've put them in column B. The first formula was intended for A1 only, the second for A2:A18.
Regards
Another one
D2:D19
=AGGREGATE(15,6,ROW(D$1:D$36)/ISNA(MATCH(ROW(D$1:D$36),D$1:D1,)),RANDBETWEEN(1,ROWS(D2:D$19)))
thanks for your help my friends.actually i wanted something else and maybe i did not explained more clear.
i need to extract 18 unique numbers out of 36 ,each of a time becuasei want to make alot of groups combinations of 18 unique numbers
yes good,what we need is 18 uniwue numbers each time not the same numbers with different order,thanks boss
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks