## Assign records unique random text strings

This article demonstrates a formula that distributes given text strings randomly across records in any given day meaning they may be repeated the next day.

The image above shows dates in column B, names in column C and a formula in column D that randomly extracts cell values from column G.

It will make sure that there are no duplicate text strings in any given day.

**Vijay asks: **

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

The objects are unique within that day. It means that a person can get the same object the next day if unlucky. If this is not what you are looking for, let me know.

The array formula in cell D3:D32 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 D3:

**How to enter an array formula**

- Select cell D3
- Copy above array formula.
- Paste to cell C3.
- Press and hold CTRL + SHIFT simultaneously.
- Press ENTER once.
- Release all keys.

The array formula begins and ends with curly brackets, like this: {=*array_formula*}. These characters appears automatically and indicate to the user that this is an array formula. Don't enter them yourself.

Excel 365 subscribers have now access to dynamic arrays meaning they don't need to enter the formula as an array formula. Simply press Enter.

This formula returns a single value per cell and will not automatically spill values to cells below.

### Explaining array formula in cell D3

(*The formula above is not the formula used in this article*)

I recommend that you use the "Evaluate Formula" tool located on the ribbon tab "Formulas" to examine any cell formula. This way you can see the all the calculation steps made and also more easily troubleshoot the formula.

- Select cell D3.
- Go to tab "Formulas" on the ribbon.
- Click the "Evaluate Formula" button and a dialog box appears, see image above.
- Click on "Evaluate" button located on the dialog box to move to the next calculation step.
- Continue with step 3 until you have seen all calculation steps or click the "Close" button to dismiss the dialog box.

#### Step 1 - Identify unique text strings on a given day

The COUNTIFS function counts how many rows meet given criteria. In this case the COUNTIF function returns an array of values, each value in the array corresponds to the position in the unique text string list which is located in $G$3:$G$32.

COUNTIFS(*criteria_range1*, *criteria1*, [*criteria_range2*, *criteria2*]…)

COUNTIFS($D$2:D2,$G$3:$G$32,$B$2:B2,B3)=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}

This means that all text strings can be randomly selected. If there had been previous object on the same day they would have been ruled out meaning their position in the array would be 1 or higher.

#### Step 2 - Calculate row numbers

The ROW function calculates the row number from each cell in cell range $G$3:$G$32 and returns an array of row numbers.

MATCH(ROW($G$3:$G$32),ROW($G$3:$G$32))

becomes

MATCH({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; 31; 32},{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; 31; 32})

The MATCH function calculates the relative position of each value in the array against the same array. This step makes sure that the array starts with 1 and increments with 1 up to the total number of values in cell range $G$3:$G$32.

MATCH({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; 31; 32},{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; 31; 32})

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}

Now we need to multiply the arrays to get the corresponding row number. If the first array contains a zero the result will also be a 0 (zero) for that particular value in the array.

(COUNTIFS($D$2:D2,$G$3:$G$32,$B$2:B2,B3)=0)*MATCH(ROW($G$3:$G$32),ROW($G$3:$G$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}

This array tells us that the formula in cell D3 can pick any of the text strings in column G which makes sense. None of the values have been displayed in cells above cell D3 for that particular date.

#### Step 3 - Return a random number

In this step, the COUNTIF function counts how many times the corresponding date for cell D3 has occurred in previous cells above.

There are 30 cells in column G that contains text strings, we can't return a random value from column G if we don't keep track of how many values we have displayed in cells above for that particular date.

The first argument in the COUNTIF function contains a cell reference that expands when you copy the cell and paste to cells below as far as needed.

RANDBETWEEN(1,31-COUNTIF($B$2:B3,B3))

becomes

RANDBETWEEN(1, 31-1)

becomes

RANDBETWEEN(1, 30)

The RANDBETWEEN function returns a random value between the numbers you specify.

RANDBETWEEN(1, 30)

returns a random value between 1 and 30.

#### Step 4 - Return the k-th largest number

The LARGE function returns the k-th largest number from an array or cell range.

LARGE(array, k)

LARGE((COUNTIFS($D$2:D2,$G$3:$G$32,$B$2:B2,B3)=0)*MATCH(ROW($G$3:$G$32),ROW($G$3:$G$32)),RANDBETWEEN(1,31-COUNTIF($B$2:B3,B3)))

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

The INDEX function returns a value from cell range $G$3:$G$32 based on the random row number we calculated in the previous step.

INDEX($G$3:$G$32,LARGE((COUNTIFS($D$2:D2,$G$3:$G$32,$B$2:B2,B3)=0)*MATCH(ROW($G$3:$G$32),ROW($G$3:$G$32)),RANDBETWEEN(1,31-COUNTIF($B$2:B3,B3))))

becomes

=INDEX($G$3:$G$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 text string.

### Recommended links

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. […]

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 […]

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

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

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

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 […]

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 sorted from A to Z

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

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 records unique random text strings”

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

Paste image link to your comment.

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