## Assign each person with randomly unique objects as a daily activity

**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:**

**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

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

This article describes how to create a random playlist based on a given number of teams using an array formula. […]

This blog article describes how to create teams randomly. There are twenty names in column B and four teams in […]

Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]

JD asks in this post: Dynamic team generator Hi, what if we have different number of people per team? So in […]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Extract a unique distinct list and sum amounts based on a condition

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]

### 3 Responses to “Assign each person with randomly unique objects as a daily activity”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

Hi Oscar,

I am trying to do design a random selector similar to this one, the only difference being that the formula I am using selects a random value from a table array using Vlookup.

So far I have a formula of

=VLOOKUP(RANDBETWEEN(1,COUNT($C:$C)),$C$1:$D$10,2,FALSE)

This is cells A1 - A10, there is a numerical place value (and assists with the max value for the randbetween part) in column C, and in column D are the matches. This makes it easy for me to expand/reduce the range quickly and works reasonably well. However, how can I add a condition to make it return a unique value instead of just any, (so that all ten values are alloted) whilst keeping the ease of expansion as the number of matches and returns may differ considerably, but is always at least a 1:1 ratio.

Thanks,

Chris

Chris G,

Array formula in cell B2:

Download excel *.xlsx file

Random-unique-values.xlsx

Dear Oscar,

Thank you for your efforts. I was looking for the same and came to your post and excel file. I have extended the list as I have a longer list. I'm having an issue that only first value is duplicating whereas the rest it is fine. After keep pressing F9 again and again; the duplicate disappear at time but later again it disappear.

(Screenshot attached)

https://s4.postimg.org/s7is63uu5/Screenshot_2017-08-01_15.52.02.png

can you please help me.