## 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.
- Press with left mouse button on the "Evaluate Formula" button and a dialog box appears, see image above.
- Press with mouse 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 press with left mouse button on 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

### Random category

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

This article demonstrates macros that create different types of round-robin tournaments. Table of contents Basic schedule - each team plays […]

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

Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]

The RAND() function In Excel returns a number greater than or equal to 0 (zero) and less than 1. Combining […]

Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]

### Unique distinct values category

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

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

This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]

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

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

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

This article demonstrates formulas that extract unique distinct values and ignore blank empty cells. Table of contents Extract a unique […]

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula? Answer: […]

This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

Question: How do I create a unique distinct list where adjacent cell values contain a search string? AA102 CA AA103 […]

Filtering unique distinct text values and sorting them based on the sum of adjacent values can easily be done by […]

EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]

This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]

Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]

Question: How do I create a unique distinct list from a column sorted A to Z using array formula? Array […]

This article demonstrates formulas that list unique distinct values if they contain a specified substring. Table of contents Extract unique […]

Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]

Question: I want a unique distinct list to be created from a column where an adjacent column has text cell […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

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

Get the 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.