Vijay asks:
I've 30 objects and 10 people, and I need to assign each person with randomly (unique) selected objects as a daily activity. Would you please suggest me on how I can do it using excel formulae?
Answer:

The objects are unique within that day. It means that a person can get the same object next day if unlucky. If this is not what you are looking for, let me know.
The array formula in cell C3:C32 returns a random unique value every time you press F9. The objects are in column F. The table does not need to be sorted by date.
Array formula in cell C3:
=INDEX($F$3:$F$32, LARGE((COUNTIFS($C$2:C2, $F$3:$F$32, $A$2:A2,A3)=0)*MATCH(ROW($F$3:$F$32), ROW($F$3:$F$32)), RANDBETWEEN(1, 31-COUNTIF($A$2:A3, A3))))
How to enter an array formula
- Select cell C3
- Copy/Paste above array formula to formula bar
- Press and hold CTRL + SHIFT
- Press ENTER
Explaining array formula in cell C3
Step 1 - Calculate possible objects
COUNTIFS($C$2:C2, $F$3:$F$32, $A$2:A2,A3)=0
becomes
{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}=0
and returns
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}
All objects can be randomly selected. If there had been previous object that day they would have been ruled out.
Step 2 - Calculate row numbers
(COUNTIFS($C$2:C2, $F$3:$F$32, $A$2:A2,A3)=0)*MATCH(ROW($F$3:$F$32), ROW($F$3:$F$32))
becomes
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}*{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30}
and returns {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30}
Step 3 - Return a random number
RANDBETWEEN(1, 31-COUNTIF($A$2:A3, A3))
becomes
RANDBETWEEN(1, 31-1)
becomes
RANDBETWEEN(1, 30)
Step 4 - Return the k-th largest number
LARGE(array, k)
LARGE((COUNTIFS($C$2:C2, $F$3:$F$32, $A$2:A2,A3)=0)*MATCH(ROW($F$3:$F$32), ROW($F$3:$F$32)), RANDBETWEEN(1, 31-COUNTIF($A$2:A3, A3)))
becomes
LARGE({1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30}, RANDBETWEEN(1, 30))
and returns a random row number between 1 and 30.
Step 5 - Return a value
=INDEX($F$3:$F$32, LARGE((COUNTIFS($C$2:C2, $F$3:$F$32, $A$2:A2,A3)=0)*MATCH(ROW($F$3:$F$32), ROW($F$3:$F$32)), RANDBETWEEN(1, 31-COUNTIF($A$2:A3, A3))))
becomes
=INDEX($F$3:$F$32, random_row_number)
becomes
=INDEX({"A"; "B"; "C"; "D"; "E"; "F"; "G"; "H"; "I"; "J"; "K"; "L"; "M"; "N"; "O"; "P"; "Q"; "R"; "S"; "T"; "U"; "V"; "X"; "Y"; "Z"; "AA"; "BB"; "CC"; "DD"; "EE"}, random_row_number)
and returns a random object.
Download excel *.xlsx file
Assign each person with randomly (unique) selected objects as a daily activity.xlsx
Hi Oscar,
I am in love with.. your formula explanation..
Waiting eagerly for MMULT & some D-Functions..
Regards,
Deb
Debraj Roy,
Thank you!
I am curious, in what situation do you use MMULT?
Hi Oscar,
We can use MMULT in all cases where SUMPRODUCT fails..
with only Two Criteria..
* Only TWO Array can be multiplied..
* 1st Array's No Of Row.. Should be Same as 2nd Array's No Of Column..
Unlike SUMPRODUCT, It returns ARRAY output..
I think, Binary Addition & Binary Multiplication are the base of all FORMULA's & FUNCTION..
and you are doing a great job, by teaching/using them in your daily blog..
Regards!
Deb
Debraj Roy,
Well, I am learning from you right now.
Can you provide an example where SUMPRODUCT fails and MMULT succeeds?
I searched and found my old mathematics books from college, I had forgotten the basics of multiplying two matrices.
It is worthwhile mentioning that in both Small and Large K could also be an array
So if A1:A10 contains random numbers the below formulas
=Large(A1:A10,{1,2,3}) - Return an array containing the top 3 numbers
=SUM(Large(A1:A10,{1,2,3}) -Array entered Returns the Sum of the top 3 numbers
=SUM(LARGE(A1:A10,ROW(INDIRECT("1:"&TopN))))- Array Entered Returns the sum of the Top N numbers as defined in the Cell/Named Constant TopN
=Large(A1:A10,Row(A1:A10))- Array entered returns an array of numbers in A1:A10 in Descending order
Likewise Small
sam,
It is worthwhile mentioning that in both Small and Large K could also be an array
Yes you are right! Thanks for pointing that out.