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

### Category: Random

How to create a list of random unique numbers in excel

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]Comments(102) Filed in category: Excel, Random

Select 6 unique and random numbers from 1 to 49 (Lottery) in excel

Introduction This article describes how to randomly select 6 unique numbers from 1 to 49. In a 6-from-49 lotto, a […]Comments(16) Filed in category: Excel, Random

Create a random playlist in excel

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]Comments(10) Filed in category: Excel, Random

Dynamic team generator in excel

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 […]Comments(9) Filed in category: Excel, Random, Team generator

Comments(7) Filed in category: Excel, Random, Team generator

### Category: Unique distinct values

5 easy ways to extract unique distinct values

Update: 30 Aug, 2017h You have quite a few options to choose from if you are looking for a way […]Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values, Unique values

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 an unique distinct […]Comments(79) Filed in category: Excel, Unique distinct values

Create a unique distinct alphabetically sorted list extracted from a column

The formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique […]Comments(53) Filed in category: Excel, Unique distinct values

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 […]Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

Extract a unique distinct list by matching items that meet a criterion in excel

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]Comments(40) Filed in category: Excel, Unique distinct values

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

### Leave a Reply

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

<code>your formula</code>

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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.