## How to extract a unique distinct list from a column in excel

You have quite a few options to choose from if you are looking for a way to create a unique distinct list in your workbook, all demonstrated in this post or on this website.

I would choose the advanced filter if you want to quickly create a unique distinct list. It is fast and easy.

If you know that you will be extracting unique distinct values from time to time in the same workbook I would recommend using a formula and an excel defined table. You won't need to repeat the same steps over and over compared to the advanced filter and that will save you time and repetitive work.

However working with a large data set may slow down the formula calculations considerably depending on your computer hardware, so perhaps the User Defined Function [UDF] is a better choice or even better a pivot table, if you have huge amounts of data to work with.

The pivot table is lightning fast even with huge data tables but it does have a little learning curve and it requires a few steps to set it up but in my opinion it is totally worth learning how to use pivot tables.

I have made an add-in that lets you extract unique, unique distinct and duplicate values and records from multiple worksheets. This allows you to easily bring together data from multiple sources in your workbook.

**Table of Contents**

- How to extract unique distinct values from a column [Formula]
- Extract unique distinct values (case sensitive) [Formula]
- How to filter unique values from a list [Formula]
- How to extract unique distinct values from a column using advanced filter [Excel feature]
- User Defined function: Extract unique distinct sorted values from a cell range (vba) [UDF]
- Build a list of unique distinct values [Pivot Table]
- Filter unique distinct values and records from multiple sheets add-in
- Useful tips
- Useful links
- Functions in all above formulas

### Create a list of unique distinct values

Unique distinct values are all cell values but duplicate values are removed.

**Example sheet - How to remove duplicate values
**

Column A contains names, some cells have duplicate values. An array formula in column B extracts an unique distinct list from column A.

**Array formula in cell B2:**

*Thanks to Eero, who contributed the original array formula!*

or use this regular formula:

**Formula in cell B2:**

This post shows you how to: Filter unique distinct row records

#### How to create an array formula

You don't need to follow these steps if you chose the regular formula.

- Copy the aray formula above (Ctrl + c)
- Double click cell B2
- Paste (Ctrl + v)
- Press and hold Ctrl + Shift simultaneously
- Press Enter
- Release all keys

If you made the above steps correctly the formula now has a beginning and ending curly bracket, like this:

{=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))}

Don't enter these characters yourself, they appear automatically.

Copy cell B2 and paste to cells below as far as needed.

#### How the array formula in cell B2 works

**Step 1 - Create an array with the same size as the list**

=INDEX($A$2:$A$20,MATCH(0,**COUNTIF($B$1:B1,$A$2:$A$20)**,0))

COUNTIF(range,criteria)

Counts the number of cells within a range that meet the given condition

COUNTIF($B$1:B1,$A$2:$A$20) returns an array containing either 1 or 0 based on if $B$1:B1 is found somewhere in the array $A$2:$A$20.

COUNTIF($B$1:B1,$A$2:$A$20)

becomes

COUNTIF("Unique distinct list",{Federer,Roger; Djokovic,Novak; Murray,Andy; Davydenko,Nikolay; Roddick,Andy; DelPotro,JuanMartin; Federer,Roger; Davydenko,Nikolay; Verdasco,Fernando; Gonzalez,Fernando; Wawrinka,Stanislas; Gonzalez,Fernando; Blake,James; Nalbandian,David; Robredo,Tommy; Wawrinka,Stanislas; Cilic,Marin; Stepanek,Radek; Almagro,Nicolas} )

and returns:

{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

This means the cell value in $B$1:B1 can´t be found in any of the cells in cell range $A$2:$A$20. If it had been found, somewhere in the array the number 1 would exist.

**Step 2 - Return the position of an item that matches 0 (zero)**

MATCH(lookup_value,lookup_array, [match_type] returns the relative position of an item in an array that matches a specified value.

MATCH(0,COUNTIF($B$1:B1,$A$2:$A$20),0)

becomes

MATCH(0,{**0**;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},0)

and returns 1.

**Step 3 - Return a cell value**

INDEX(array,row_num,[column_num]) returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

*=INDEX(List,1) *

becomes

=INDEX({**Federer,Roger**; Djokovic,Novak; Murray,Andy; Davydenko,Nikolay; Roddick,Andy; DelPotro,JuanMartin; Federer,Roger; Davydenko,Nikolay; Verdasco,Fernando; Gonzalez,Fernando; Wawrinka,Stanislas; Gonzalez,Fernando; Blake,James; Nalbandian,David; Robredo,Tommy; Wawrinka,Stanislas; Cilic,Marin; Stepanek,Radek; Almagro,Nicolas}, 1)

*and returns "Federer, Roger"*

**Relative and absolute cell references**

When you copy the array formula down the countif formula range (*$B$1:B1)* expands. This is created by using relative and absolute references.

The first cell, B2: *COUNTIF($B$1: B1,$A$2:$A$20)*

Second cell, B3: *COUNTIF($B$1: B2,$A$2:$A$20)*

and so on.

#### Excel 2007 users can remove errors using IFERROR() function

When the formula runs out of values it returns #N/A errors (Not Available), you can use the IFERROR function to remove the error and return blank in those cell. Unfortunately it comes with a big caveat, it also removes other formula errors as well. So use this with great caution, if your source table has errors you won't detect it because the IFERROR function returns a blank cell instead.

*and copy it down as far as necessary.*

The formula is an array formula, how to enter an array formula.

#### Excel 2003 users can remove errors using isna() function:

*and copy it down as far as needed.*

This formula is an array formula, how to enter an array formula.

#### How to handle blank cells in a range

Harlan Grove created a formula to count unique distinct values from a list with blanks. I used the same technique here to filter unique distinct values:

**Array formula in cell B2:**

#### Download excel sample file for this tutorial

Extract-a-unique-distinct-list-in-excelv4.xlsx

(Excel 2007 Workbook *.xlsx)

#### Unique distinct formulas

**Filter unique distinct row records**

Extract a list of unique distinct records and other related examples.

**Unique distinct list from two columns**

Learn how to build a formula that merges two columns to a unique distinct list

**Unique distinct list from three columns**

This post demonstrates a formula that merges three columns to a unique distinct list

**Unique distinct list from multiple columns**

How to merges multiple columns to a unique distinct list

**Unique distinct list using a condition**

Learn how to build a formula that creates a unique distinct list using a condition. The ISTEXT function identifies text values in an adjacent column.

**Create a unique distinct list using two conditions**

Construct a formula that creates a unique distinct list using two conditions. Find numbers in an interval and extract unique distinct values from an adjacent column.

### Extract a unique distinct list (case sensitive)

The following array formula lists unique distinct values from a list and it is case sensitive. Aa is not equal to AA.

**Array formula in cell C2:**

#### Explaining the array formula in cell C3

**Step 1 - Transpose previous values**

TRANSPOSE($C$1:C2)

becomes

TRANSPOSE({"Unique distinct list (case sensitive)";"Aa"})

and returns

{"Unique distinct list (case sensitive)","Aa"}

Note that the ; changes to a ,

**Step 2 - Check if two text strings are exactly the same, also case sensitive **

EXACT($A$1:$A$9, TRANSPOSE($C$1:C1))

becomes

EXACT($A$1:$A$9, TRANSPOSE({"Unique distinct list (case sensitive)","Aa"})

becomes

EXACT($A$1:$A$9, TRANSPOSE({"Unique distinct list (case sensitive)","Aa"})

becomes

EXACT({"Aa"; "CC"; "AA"; "BB"; "BB"; "EE"; "bb"; "Aa"; "aa"}, TRANSPOSE({"Unique distinct list (case sensitive)","Aa"})

and returns

{FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE}

**Step 3 - Return relative position in array if TRUE**

IF(EXACT($A$1:$A$9, TRANSPOSE($C$1:C1)), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9))

becomes

IF({FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE}, MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)))

becomes

IF({FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE}, {1;2;3;4;5;6;7;8;9})

and returns

{FALSE,1; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,8; FALSE,FALSE}

**Step 4 - Calculate how often values occur in an array**

FREQUENCY(IF(EXACT($A$1:$A$9, TRANSPOSE($C$1:C1)), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)), ""), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)))

becomes

FREQUENCY({FALSE,1; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,8; FALSE,FALSE},MATCH(ROW($A$1:$A$9),ROW($A$1:$A$9)))

becomes

FREQUENCY({FALSE,1; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,FALSE; FALSE,8; FALSE,FALSE},{1;2;3;4;5;6;7;8;9})

and returns

{1;0;0;0;0;0;0;1;0;0} Aa is found in position 1 and 8 in cell range A1:A8

**Step 5 - Find first empty value (0) in array**

MATCH(0, FREQUENCY(IF(EXACT($A$1:$A$9, TRANSPOSE($C$1:C1)), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)), ""), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9))), 0)

becomes

MATCH(0, {1;0;0;0;0;0;0;1;0;0}, 0)

and returns 2.

**Step 6 - Return value from position 2**

INDEX($A$1:$A$9, MATCH(0, FREQUENCY(IF(EXACT($A$1:$A$9, TRANSPOSE($C$1:C1)), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9)), ""), MATCH(ROW($A$1:$A$9), ROW($A$1:$A$9))), 0))

becomes

INDEX($A$1:$A$9, 2)

and returns "CC" in cell C3.

**Download excel *.xlsx file **

Extract-a-unique-distinct-list-in-excel4.xlsx

#### Case sensitive formulas

**Filter unique distinct records (case sensitive) [UDF]**

A User Defined Function to extract unique distinct records (case sensitive).

**Search case sensitive and return multiple values**

Do a case sensitive VLOOKUP and return multiple values, however the VLOOKUP function is not used here.

**Count unique distinct values (case sensitive)**

This post demonstrates a formula on how to count unique distinct values (case sensitive)in a cell range

**Count multiple text strings in a cell range (case sensitive)**

This post demonstrates a formula on how to count text strings (case sensitive) in a cell range

**How to filter unique values from a list**

Unique values are values existing only once in a list. Example, AA occurs twice in the list below and is not unique. BB and CC occur only once each and is unique in the list.

**Example sheet - Filter unique values**

Column C filters all unique values values from column A. Unique values occurs only once in column A.

Example, Roger, Federer is not in column C because there are more than one value in column A. In other words, the name is not unique in column A. You can find the name twice in the list, in cell A2 and A8.

**Array formula in C2:**

or use this regular formula:

*and copy cell C2 down as far as needed.*

#### Explaining array formula in cell C2

**Step 1 - Count each value in array and check if it is not equal to one**

(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1

becomes

{2;1;1;2;1;1;2;2;1;2;2;2;1;1;1;2;1;1;1}<>1

and returns

{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}

This array tells excel that the first value in the array is not unique and that is true because Roger, Federer is not unique in the list. However the second value is FALSE and that value is unique, etc.

**Step 2 - Keep track of previous values**

C1:$C$1 is a dynamic cell reference, it changes as the formula is copied to cells below. You can read more about absolute and relative cell references here.

COUNTIF(C1:$C$1, $A$2:$A$20)

becomes

COUNTIF("Unique list", {"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; "Davydenko, Nikolay "; "Roddick, Andy "; "Del Potro, Juan Martin "; "Federer, Roger "; "Davydenko, Nikolay "; "Verdasco, Fernando "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "; "Gonzalez, Fernando "; "Blake, James "; "Nalbandian, David "; "Robredo, Tommy "; "Wawrinka, Stanislas "; "Cilic, Marin "; "Stepanek, Radek "; "Almagro, Nicolas "})

and returns

{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}

A zero (0) means that no values have yet been displayed and that is true in cell C2. However when excel calculates the value in cell C3, cell C2 shows "Djokovic, Novak" and the array becomes {0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}. The second value in the array contains 1. This tells excel that value has already been shown.

**Step 3 - Add arrays**

COUNTIF(C1:$C$1, $A$2:$A$20)+(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1

becomes

{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE} + {0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}

and returns

{1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0}

TRUE is 1 and FALSE is zero. So True + 0 equals 1 and False + 1 equals 1.

**Step 4 - Find first zero value in array**

A zero in the array indicates {1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0} that the corresponding value is unique and has not yet been displayed in the list.

MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1), 0)

becomes

MATCH(0, {1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0}, 0)

and returns 2.

**Step 5 - Return corresponding value**

INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+(COUNTIF($A$2:$A$20, $A$2:$A$20)<>1), 0))

becomes

INDEX($A$2:$A$20, 2)

and returns "Djokovic, Novak" in cell C2.

#### Download excel sample file for this tutorial.

Extract-a-unique-distinct-list-in-excel.xls

(Excel 97-2003 Workbook *.xls)

To extract duplicates, see this post: Extract a list of duplicates from a column using array formula in excel

Back to top

### How to extract unique distinct values from a column using advanced filter

Unique distinct values are all cell values but duplicate values are merged into one distinct value.

- Go to tab "Data" on the ribbon
- Click "Advanced Filter" button on the ribbon

- Click "Copy to another location"

- Click "List range:" and select range to filter unqiue distinct values
- Click "Copy to: and select a range
- Click "Unique records only"
- Click "OK"!

#### Lean more about other powerful excel features

Advanced filter is not the only powerful built-in feature in excel, here are some more:

**Excel Pivot table**

This post shows you how pivot tables work. Pivot tables are perhaps the most powerful feature in excel and also the least known.

**Excel defined tables**

Excel defined tables lets you organize, filter and manipulate data with ease.

### User Defined function: Extract unique distinct sorted values from a cell range (vba)

**Array formula in cell B2:B8212:**

**How to create an array formula**

- Type B2:B8212 in name box
- Type above array formula in formula bar

- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys

**VBA code**

I am using the selectionsort function to sort values. You can read more about the function here:

Using a Visual Basic Macro to Sort Arrays in Microsoft Excel

Function FilterUniqueSort(rng As Range) Dim ucoll As New Collection, Value As Variant, temp() As Variant Dim iRows As Single, i As Single ReDim temp(0) On Error Resume Next For Each Value In rng If Len(Value) gt; 0 Then ucoll.Add Value, CStr(Value) Next Value On Error GoTo 0 For Each Value In ucoll temp(UBound(temp)) = Value ReDim Preserve temp(UBound(temp) + 1) Next Value ReDim Preserve temp(UBound(temp) - 1) iRows = Range(Application.Caller.Address).Rows.Count SelectionSort temp For i = UBound(temp) To iRows ReDim Preserve temp(UBound(temp) + 1) temp(UBound(temp)) = "" Next i FilterUniqueSort = Application.Transpose(temp) End Function

**Where to copy vba code?**

- Press Alt + F11
- Insert a module into your workbook
- Copy (Ctrl + c) Paste (Ctrl +v) above vba code into the code window

**Download example file**

Extract unique distinct sorted values from a cell range.xls

#### More powerful UDFs

**Filter unique distinct records (case sensitive) [UDF]**

A User Defined Function to extract unique distinct records (case sensitive).

**Lookup and return multiple values concatenated into one cell [UDF and formula]**

Use a condition to extract values and concatenate them.

**Filter unique distinct words from a cell range [udf]**

Extract only unique distinct words from a cell range

### Filter unique distinct values from multiple sheets add-in

**Filter unique distinct values **is an add-in for Excel 2007/2010/2013 that lets you extract

- unique distinct values
- duplicate values
- unique distinct records
- duplicate records

from **multiple sheets**. The Add-In contains 4 user defined functions.

If a value in one of the ranges changes the function will automatically and instantly update the list.

#### Features

- All user defined functions remove blank values and blank records.
- No error values when all values are extracted.
- Filter values or records from up to 255 different cell ranges or sheets.

#### What are unique distinct values?

#### What are unique distinct records?

#### Example - How to filter unique distinct values from multiple sheets using the Add-In

Purchase Filter Unique Distinct Values From Multiple Sheets Add-in For Excel 2007/2010/2013 - Price $19 USD

### Questions

**Is there a money back guarantee?**

Sure, you have un-conditional money back guarantee for 14 days.

### Useful tips

#### Excel tables

An Excel defined table is very cleverly designed, it is constructed to automatically expand if you add more data. You don't need to do anything.

Structured references are cell references to an excel defined table. They let you easily see what the data contains as long as you give it good descriptive column header names.

I recommend you use excel defined tables instead of named ranges or dynamic named ranges as long as you are working with more than one value.

Here is how to convert a list to an excel defined table:

- Select a cell in your list
- Go to tab "Insert" on the ribbon and click Table button or press Ctrl + T
- Click OK button
- Your excel defined table is created

Read more about excel defined tables

#### Named ranges

In excel you can name a cell range, a constant or a formula. You can then use the named range in a formula, making it easier for you to read and understand formulas.

*Example*

List : A2:A20

Tip! Use dynamic named ranges to automatically adjust cell ranges when new values are added or removed.

#### How to create a named range

The downside with named ranges is that you need to adjust the range every time you add or delete a value in the list, the named range will then not fit the value list. I recommend using excel defined tables if you know that the list may change in the future.

- Select cell range A2:A20
- Type
*List*in name box - Press Enter

**Array formula and named range in cell B2:**

#### Useful blog posts

Want to learn more about **filtering unique distinct values**? You must read these blog posts:

- Extract a unique distinct list from two columns using excel 2007 array formula
- Extract a unique distinct list from three columns in excel
- Unique values from multiple columns using array formulas
- Unique list to be created from a column where an adjacent column has text cell values
- Create unique list from column where an adjacent column meets criteria
- How to create a unique distinct list where other columns meet two criteria

Learn more about **sorting** unique distinct values. Read these blog posts:

- Extract a unique distinct list sorted from A-Z from range in excel
- Sort a range by occurence using array formula in excel
- Create a unique list and sort by occurrances from large to small

Learn more about **counting unique distinct values and records**. Read these blog posts:

Learn more about **filtering** and **comparing** unique distinct records. Read these blog posts:

- Filter unique distinct records in excel 2007
- Compare two lists of data: Highlight common records in excel
- Compare two lists of data: Filter records existing in only one list in excel
- Compare two lists of data: Filter common row records in excel
- Quickly compare two tables in excel 2007

Read more about **custom functions** in excel

#### Functions in all above array formulas

**INDEX function explained**

INDEX(array,row_num,[column_num])

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**COUNTIF function explained**

COUNTIF(

*range*,

*criteria*)

The COUNTIF function counts the number of cells within a range that meet a single criterion that you specify.

**MATCH function**

MATCH(lookup_value, lookup_array, [match_type])

Returns the relative position of an item in an array that matches a specified value in a specific order

**How to use the IFERROR function**

IFERROR(value, value_if_error)

Returns value_if_error if expression is an error and the value of expression itself otherwise.

**IF function explained**

IF(logical_test,[value_if:true],[value_if_false])

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**SUM function explained**

Learn how to use Excel SUM function and it's arguments. The SUM function adds all the numbers in a range of cells. Many examples and a workbook for you to download.

**How to use FREQUENCY function**

FREQUENCY(

*data_array*,

*bins_array*)

Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than the bins_array

**TRANSPOSE function**

TRANSPOSE(

*array*)

The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range is a range with values in one column, a horizontal range has values in one row. You can also transpose a range with more than one column and one row.

**EXACT(**text1,[text2],**)
**Checks whether two text strings are exactly the same, and returns TRUE or FALSE. EXACT is case sensitive.

### Related articles

#### Advanced-filter-excel

The VLOOKUP function is designed to return only the corresponding value of the first instance of a lookup value. But there is a work-around to identify multiple matches. Although VLOOKUP is not used in these array formulas, they are easier to […]

The TEXTJOIN function introduced in excel 2016 allows you to concatenate values easily. It also accepts arrays and nested functions. However if your excel version is missing the TEXTJOIN function you can use a User Defined Function, I have all […]

Question: How do I create a chart that dynamically adds the values, as i type them in the workbook? Answer: Excel 2007 (and later versions) users, create an excel defined table. Excel 2003 (and earlier versions) users, create a dynamic […]

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no luck. How do you find multiple occurrences? Do I need to use match function? Answer: Table of Contents How to […]

You have quite a few options to choose from if you are looking for a way to create a unique distinct list in your workbook, all demonstrated in this post or on this website. I would choose the advanced filter […]

Question: I have a table of 3 coloms (Security name, date, price) and I have to find the price a a security at a certain date in a table that contain many securities and prices for this securities for different […]

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09 Cell B1 1/11/09-1/17/09 Cell C1 1/18/09-1/24/09 How do I create a formula to do this? Answer: See row 3 and formula below […]

Here is a list of order numbers and products. We are going to create two drop down lists. The first drop down list contains unique distinct values from column A. The second drop down list contains unique distinct values from […]

Problem: Remove blank cells from a list of values? How to create a list with non empty cells? I want to create a new list without blanks. Answer: In this blog post I´ll provide two solutions on how to remove […]

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

#### Excel

The VLOOKUP function is designed to return only the corresponding value of the first instance of a lookup value. But there is a work-around to identify multiple matches. Although VLOOKUP is not used in these array formulas, they are easier to […]

The TEXTJOIN function introduced in excel 2016 allows you to concatenate values easily. It also accepts arrays and nested functions. However if your excel version is missing the TEXTJOIN function you can use a User Defined Function, I have all […]

Question: How do I create a chart that dynamically adds the values, as i type them in the workbook? Answer: Excel 2007 (and later versions) users, create an excel defined table. Excel 2003 (and earlier versions) users, create a dynamic […]

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no luck. How do you find multiple occurrences? Do I need to use match function? Answer: Table of Contents How to […]

You have quite a few options to choose from if you are looking for a way to create a unique distinct list in your workbook, all demonstrated in this post or on this website. I would choose the advanced filter […]

Question: I have a table of 3 coloms (Security name, date, price) and I have to find the price a a security at a certain date in a table that contain many securities and prices for this securities for different […]

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09 Cell B1 1/11/09-1/17/09 Cell C1 1/18/09-1/24/09 How do I create a formula to do this? Answer: See row 3 and formula below […]

Here is a list of order numbers and products. We are going to create two drop down lists. The first drop down list contains unique distinct values from column A. The second drop down list contains unique distinct values from […]

Problem: Remove blank cells from a list of values? How to create a list with non empty cells? I want to create a new list without blanks. Answer: In this blog post I´ll provide two solutions on how to remove […]

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

#### Unique-distinct-values

The VLOOKUP function is designed to return only the corresponding value of the first instance of a lookup value. But there is a work-around to identify multiple matches. Although VLOOKUP is not used in these array formulas, they are easier to […]

The TEXTJOIN function introduced in excel 2016 allows you to concatenate values easily. It also accepts arrays and nested functions. However if your excel version is missing the TEXTJOIN function you can use a User Defined Function, I have all […]

Question: How do I create a chart that dynamically adds the values, as i type them in the workbook? Answer: Excel 2007 (and later versions) users, create an excel defined table. Excel 2003 (and earlier versions) users, create a dynamic […]

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no luck. How do you find multiple occurrences? Do I need to use match function? Answer: Table of Contents How to […]

You have quite a few options to choose from if you are looking for a way to create a unique distinct list in your workbook, all demonstrated in this post or on this website. I would choose the advanced filter […]

Question: I have a table of 3 coloms (Security name, date, price) and I have to find the price a a security at a certain date in a table that contain many securities and prices for this securities for different […]

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09 Cell B1 1/11/09-1/17/09 Cell C1 1/18/09-1/24/09 How do I create a formula to do this? Answer: See row 3 and formula below […]

Here is a list of order numbers and products. We are going to create two drop down lists. The first drop down list contains unique distinct values from column A. The second drop down list contains unique distinct values from […]

Problem: Remove blank cells from a list of values? How to create a list with non empty cells? I want to create a new list without blanks. Answer: In this blog post I´ll provide two solutions on how to remove […]

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

#### User-defined-functions-udf

The VLOOKUP function is designed to return only the corresponding value of the first instance of a lookup value. But there is a work-around to identify multiple matches. Although VLOOKUP is not used in these array formulas, they are easier to […]

The TEXTJOIN function introduced in excel 2016 allows you to concatenate values easily. It also accepts arrays and nested functions. However if your excel version is missing the TEXTJOIN function you can use a User Defined Function, I have all […]

Question: How do I create a chart that dynamically adds the values, as i type them in the workbook? Answer: Excel 2007 (and later versions) users, create an excel defined table. Excel 2003 (and earlier versions) users, create a dynamic […]

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no luck. How do you find multiple occurrences? Do I need to use match function? Answer: Table of Contents How to […]

You have quite a few options to choose from if you are looking for a way to create a unique distinct list in your workbook, all demonstrated in this post or on this website. I would choose the advanced filter […]

Question: I have a table of 3 coloms (Security name, date, price) and I have to find the price a a security at a certain date in a table that contain many securities and prices for this securities for different […]

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09 Cell B1 1/11/09-1/17/09 Cell C1 1/18/09-1/24/09 How do I create a formula to do this? Answer: See row 3 and formula below […]

Here is a list of order numbers and products. We are going to create two drop down lists. The first drop down list contains unique distinct values from column A. The second drop down list contains unique distinct values from […]

Problem: Remove blank cells from a list of values? How to create a list with non empty cells? I want to create a new list without blanks. Answer: In this blog post I´ll provide two solutions on how to remove […]

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

#### Vba

The VLOOKUP function is designed to return only the corresponding value of the first instance of a lookup value. But there is a work-around to identify multiple matches. Although VLOOKUP is not used in these array formulas, they are easier to […]

The TEXTJOIN function introduced in excel 2016 allows you to concatenate values easily. It also accepts arrays and nested functions. However if your excel version is missing the TEXTJOIN function you can use a User Defined Function, I have all […]

Question: How do I create a chart that dynamically adds the values, as i type them in the workbook? Answer: Excel 2007 (and later versions) users, create an excel defined table. Excel 2003 (and earlier versions) users, create a dynamic […]

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no luck. How do you find multiple occurrences? Do I need to use match function? Answer: Table of Contents How to […]

You have quite a few options to choose from if you are looking for a way to create a unique distinct list in your workbook, all demonstrated in this post or on this website. I would choose the advanced filter […]

Question: I have a table of 3 coloms (Security name, date, price) and I have to find the price a a security at a certain date in a table that contain many securities and prices for this securities for different […]

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09 Cell B1 1/11/09-1/17/09 Cell C1 1/18/09-1/24/09 How do I create a formula to do this? Answer: See row 3 and formula below […]

Here is a list of order numbers and products. We are going to create two drop down lists. The first drop down list contains unique distinct values from column A. The second drop down list contains unique distinct values from […]

Problem: Remove blank cells from a list of values? How to create a list with non empty cells? I want to create a new list without blanks. Answer: In this blog post I´ll provide two solutions on how to remove […]

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

### 102 Responses to “How to create a list of random unique numbers in excel”

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

How to transpose it into colomn?

Fajar,

Array formula in B2:

Cell A2 is empty. Copy cell B2 to cell range C2:O2.

@ Oscar

Thank you for your quick reply.

Your suggestion is definitely work.

Thank you.

The sample file works in Excel 2003 while pressing F9, but if using F2 and ctrl+shift+enter on B3 for example, the result is #VALUE! even if not changing anything.

Formula evaluation shows in Step 5: RANDBETWEEN(1,{10}) with 10 in {} that evaluates to #VALUE.

As a workaround, I've added the first function that came to mind (SUM) to change the array with 1 item to a single value.

RANDBETWEEN(1,SUM(ROW(A1)))

Hallo, What if my number of rows will vary and I do not want to manually change the formula on different sheets?

Adell,

Array formula in cell B5:

$B$4:B4, ROW(INDEX($A$1:$A$1000, $C$1):INDEX($A$1:$A$1000, $C$2)))), RANDBETWEEN(1, $C$2-$C$1-ROW(A1)+2)), "")You still have to adjust one cell range (bolded).

Thank you. I managed to get it working with using the Index and And function, pointing to my "count' value of maximum number required and it works like a charm!

I have some official user list which i want to use in random page number wise like attached example.

Please help me asap.

https://lh4.ggpht.com/1vrCVTerrixozegO8AyvQzUd7SNCWPeCv1oHKRUbJbIZ3UodpI_U8LdnvUs75yWPD3lanbc=s160

I have tried changing this formula to give me a random list of numbers from 1-189 but it doesn't work. Ideally what I want is a grid 15 by 27 full of numbers from 1-405. I was going to use this formula to give a list and paste them into my grid. Can anyone help?

mATT

Array formula in cell A2:

Copy cell A2 and paste to cell range A3:A190.

See attached file:

unique-random-list-of-numbers-1-189.xls

I know this post is a few years old. I need this exact solution, to randomly generate numbers without duplicates however in this case from 1 to 271. I have copied the above formula and the attached file to expand the range, though any modifications to either always results in #NUM error. Do you know why this is the case?

Following modified formula may help

={INDEX($B$17:$B$33, LARGE(MATCH(ROW($B$17:$B$33), ROW($B$17:$B$33))*NOT(COUNTIF($E$17:E21, $B$17:$B$33)), RANDBETWEEN(1, ROWS($B$17:$B$33)-COUNTA($E$17:E21))))}

Notice counta used at the end. This formula can be used starting from any row whereas the default one had to start at row 2.

Please note that you have to modify cell references since I have directly copied from relevant places in my excel sheet. Inconvenience regretted.

In formula counta function has been used in place of count since the purpose was to use this formula to generate random list of alphanumeric codes from list in a cell range.

I cannot seem to configure this formula to draw from an adjustable range taken from two cells.

Ex: I want a list of unique random numbers between whatever is entered in cell A4(Min) and B4(Max).

Any help would be greatly appreciated.

Array formula in cell B3:

See attached file:

unique-random-list-of-numbers-will.xls

Thank you Oscar. This only seems to work if the minimum is very low. Once you reach a minimum number>6 it begins to have problems. I am hoping to begin with numbers in the 1000-3000 range.

I wonder if you are seeing a similar problem?

Will,

you are right!

See attached file:

unique-random-list-of-numbers-will2.xls

This is brilliant Oscar. Thank you.

Thanks for this tip, but for some weird reason I still got a few duplicates. Is there a simple method to generate unique random numbers in a column?

I have a list of 1000 names in column A, and would like to generate unique integer numbers in column B for each of those names.

Thanks

My guess is that the cell reference (bolded below) is not changed.

=LARGE(ROW($1:$10)*NOT(COUNTIF(

$B$2:B2,ROW($1:$10))),RANDBETWEEN(1,11-ROW(A1)))The array formula above is entered in cell range B3 and down as far as needed.

If you enter the formula in cell D4 and downwards, you must change the cell reference to $D$3:D3, like this:

=LARGE(ROW($1:$10)*NOT(COUNTIF(

$D$3:D3,ROW($1:$10))),RANDBETWEEN(1,11-ROW(A1)))Hi Will,

This is a formula I used, the formula was in B3 down to B45 (teh length of my worksheet), the maximum was calcultated in B1, that is the formula revers to B1.

Hope it helps

{=LARGE(ROW(INDIRECT("$1:$"&B$1))*NOT(COUNTIF($B$2:B2,ROW(INDIRECT("$1:$"&B$1)))),RANDBETWEEN(1,B$1+1-ROW(B1))))}

Hi Will,

This formula i just posted, calculted, unique random numbers between 1 and X. Where the maksimum (X)changed on every schedule. My minimum is fixed at 1, but you can change the minimum to refer to a cell that indicated the minimum value/qty.

Good luck

Hi Adell,

I am having trouble changing the minimum value to anything but 1. It has a tendency to result in #NUM! or 0.

EX for 10-20 if B1=20:

{=LARGE(ROW(INDIRECT("$10:$"&B$1))*NOT(COUNTIF($B$2:B2,ROW(INDIRECT("$10:$"&B$1)))),RANDBETWEEN(10,$B$1+1-ROW(B1)))}

Can you tell me what I am doing wrong?

Thanks for all the help!

Hi Will,

The #num is usually, because you did not 'activate' the "string", that is the "{ }" in the beginning and end of the formula. Because there is more than one formula/statement that needs to be "true", before the calculation is done,you need to 'tell' excel to do 'all'. To do this (old fashioned way) you need to go to the beginning of your formula, before the "=" and hold down CTRL + SHFT + ENTER, then the "{}" will appear.

I had a look at the formula and have entered the minimum value into E2.

{=(LARGE(ROW(INDIRECT("$"&E$2&":$"&B$1))*NOT(COUNTIF($B$2:B2,ROW(INDIRECT("$"&E$2&":$"&B$1)))),RANDBETWEEN(E$2,B$1+1-ROW(B1))))}.

see the indirect sections as well as the randbetween part, where it stipulated the minimum and maximum values.

hope this helps, if not, shout :) (I don't know if you can obtain my email address from the webmaster if you need to contact me directly) (I am in RSA and will be going offline within the next hour - weekend! - and will only be back on Monday)

Adell

Will,

I also see that on your formula, you 'left out' the first and last set of brackets, that also might be part of your initial error.

=RANDBETWEEN(TIME(8,0,0),TIME(9,45,0))

i have to maintain random time between this nut is is not working

mayur,

randbetween works only with whole numbers.

Try this: =RAND()*(TIME(9,45,0)-TIME(8,0,0))+TIME(8,0,0)

Hi Frnd,

I want to get a random value between 1-20 but I not able to make any sucess ,I used the formula [=LARGE(ROW($1:$20)*NOT(COUNTIF($A$1:A1,ROW($1:$20))),RANDBETWEEN(1,21-ROW(A1)))]

But it is not working.Please help me...

Manoj,

your formula works, did you enter it as an array formula?

I tried on various systems with above code but i still get same error as below :

#NUM!

Please help me as it is very urgent for me.

Hi,

How to do tht or how do i make an array, because i tried as u guided above , please help me as it is very urgent

Hi ,

in your initial formula, you have "[". it should be "{". enter your formula, without the brackets before the "=" and the end one. Then, go to the beginning of your formula, to the left of the "=" and simultaneously press Ctrl Shft Enter . the "{" brackets will appear and your value will appear. (formula will work)

Hi,

I have done asu said but i get the error as: #VALUE!

and do not get the number ,please help

I used the below formula:

{=LARGE(ROW($1:$20)*NOT(COUNTIF($A$1:A1, ROW($1:$20))),RANDBETWEEN(1,21-ROW(A1)))}

Hi,

I have taken your formula, above and copied and pasted it into a new spreadsheet. took out the "{" and "}" and redid the CTRL SHFT ENTER to create the array/string formula and it works om my schedule. The only thing, that might through you out is the space between the comma and "ROW". go to the following link: http://speedy.sh/aMyaR/a.xlsx

You should be able to open it and see your formula working.

cheesh!.. sorry, it should be "throw you out" .... auto correct...

Hi ,

in your initial formula, you have "[". it should be "{". enter your formula, without the brackets before the "=" and the end one. Then, go to the beginning of your formula, to the left of the "=" and simultaneously press Ctrl Shft Enter . the "{" brackets will appear and your value will appear.

Hi,

I have done asu said but i get the error as: #VALUE!

and do not get the number ,please help

I used the below formula:

{=LARGE(ROW($1:$20)*NOT(COUNTIF($A$1:A1, ROW($1:$20))),RANDBETWEEN(1,21-ROW(A1)))}

Manoj,

Did you manage to download the spreadsheet I uploaded? Are your formula now working?

Adell,

Thank you for yor support but I'm sorry as when I click on the given link from u ,I get security error n the the downlod link closes n not allowing me to download the file . Is it possible for you sir to mail me the file on the following email id : manoranjan.tiwari@bt.com

Regards

Manoj

Hi Adell,

Is it possible to make a randome number set thro entire sheet: i tried the following formula :

[=LARGE(ROW($1:$65535)*NOT(COUNTIF($C$1:C1,ROW$1:$65535))),RANDBETWEEN(1,65536-ROW(C1)))]

but it seems not working:

whenever i try to edit your formulla n ammmend it according to my own chice , it gives error as "#NUM!" and when i pres ctrl+shift+enter , it gives this error "#VALUE!" , can you pls explain wht needs to be done to do it or can you pls make for me and send on : manoranjan.tiwari@bt.com

Thanks in advance.

Manoj,

You have a typo in your formula:

=LARGE(ROW($1:$65535)*NOT(COUNTIF($C$1:C1, ROW($1:$65535))), RANDBETWEEN(1, 65536-ROW(C1)))

I tried your formula and it works but it is extremely slow. Do you have to use formulas?

Also remember that the formula creates unique distinct random numbers. If you are looking for only random numbers use this:

=randbetween(0, 65535)

How can I make it start at 0? i.e. random numbers from 0 to 10?

=randbetween(0,10)

Can you try that? I tried that beforehand and it doesn't work for me. :( Why would 1,11 be 1-10 and 0,10 be 0 to 10?

I also want to have them lower down than rows 2-11 but can't make that work either. I tried changing the ROW($1:$10) bits but doesn't seem to work.

me,

try this formula in cell B3:

[...] An array formula taken from here..... How to create a list of random unique numbers in excel | Get Digital Help - Microsoft Excel resource [...]

[...] A couple of links that may help you: Learn Excel 2010 - "Random with No Repeats": Podcast #1471 - YouTube How to create a list of random unique numbers in excel | Get Digital Help - Microsoft Excel resource [...]

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?

Vijay,

See this post: Assign each person with randomly unique objects as a daily activity

[...] Vijay asks: [...]

why does the code change the values in Columns "B" and "C" when I enter text values and tab to the next column?

This is what I'm Using, and I only want to affect Column "A" values:

=LARGE(ROW($1:$1000)*NOT(COUNTIF($A$1:A4, ROW($1:$1000))), RANDBETWEEN(1,1000-ROW(A4)))

Correction:

I should have copied the correct code (and lessened the number of tests):

=LARGE(ROW($1:$20)*NOT(COUNTIF($A$1:A1, ROW($1:$20))), RANDBETWEEN(1,20-ROW(A1)))

And, I should've said that when I enter a value in any other column, it automatically assigns a new random number to the fields in column "A". how to do I get it to assign a random number to column "A"'s fields without being readjusted when I enter any other information in the other columns???

Hi Paul,

Random numbers mean excel chooses a random number every time you refresh. (enter, tab, move etc)

Please give me more information as to what info do you want in A, B and C?

Regards

Adell,

I was looking for a way to have excel create a unique random number (not duplicated) in Column "A" that is not influenced by another key stroke. So, for instance, let's say I am creating a patient record and I want to give their account a uniqe number (almost like a primary key in Access or SQL). I don't want that number to be repeated, or to change as I enter more information in the next columns....

You might, once the number has randomly been created make it a definite number and not a formula anymore by: copy, paste special, values.

Or, for a patient number, for instance is to use say the first 3 letters of their surname [use this formula is the surname is in column B =LEFT(B2,3)] with a number, starting at 00001 to infinity. Have excel check that this combination has not been used before?

Let me know if this would work for you.

I think the second option, in using the first 3 letters of the surname with a random number may be the best option in this case. How would you recommend going about doing that???

Paul,

Perhaps like this:

As per Oscar's formula, your numbers will be sequential, which is great, for record keeping and all rules and regulations, that I have ever come in contact with, regarding assigning of record numbers. If you combine your random number formula in stead of the countif formula, you will still have the issue of the numbers changing every time, unless you copy/paste/special after assigning a number. Good luck

Hi Oscar,

Excellent article - with the steps explained.

Only I could not understand why the randbetween is restricted to lesser and lesser values as the range progresses.

RANDBETWEEN(1,20-ROW(A1)))

Request you to explain the logic.

Oscar -

Trying to convert this into a formula for creating a random list from another source. For example, if I have a list of the 50 U.S. states I'd like to create a random/unique list of x number of states. I'm using the position of the state in the INDEX to use as the random number but I'm having trouble in the COUNTIF statement to relate the prior state names I've returned to the "used" portion of the COUNTIF array.

GMF,

Generate unique random text strings

In Excel 2003, when I copy your formula into the formula bar, (cell A2), and use CTRL-SHIFT-ENTER, I get #VALUE! for a result. When I copy the cell directly from your worksheet, (in that case cell B3), and paste it into a blank worksheet, it works fine. Examining that formula it shows #VALUE! for 'RANDBETWEEN(1,11-ROW(A1))', but still works. If I click on the formula bar and then CTRL-SHIFT-ENTER, that formula stops working and returns #VALUE!. I can work around it by replacing ROW(A1) by 1, ROW(A2) by 2, etc.; or 11-ROW(A1) by 10,,etc. – but for sanity's sake, I would like to know what Excel magic you use to make your formula work. Thanks.

Hi Oscar,

I am trying to create a random list of of ten one digit numbers between 0 and 9. It gives me a formula error when I try to do it. Any assistance would be greatly appreciated.

Regards,

Gavin

Gavin,

Did you enter the formula as an array formula? (Instructions above)

Hi All,

Would really appreciate it if someone can help me.

I am trying to generate random numbers between -300 & 300.

The formula above seems to work for positive numbers only?

=LARGE(ROW($1:$300)*NOT(COUNTIF($A$1:A1, ROW($1:$300))), RANDBETWEEN(1,300-ROW(A1)))

How can I make this formula generate negative and positive ( -300 to 300)

Thanks

Jack,

Yes, you are right.

Try this:

=LARGE(ROW($1:$600)*NOT(COUNTIF($A$1:A1, ROW($1:$600))), RANDBETWEEN(1,600-ROW(A1)))-300

Thanks Oscar you are the best.

I tried it but some numbers are omitted and others are repeated. Would it be okay to email you directly?

Thank you so much

Jack

Jack,

Sorry, you are right. Try this in cell A2:

=LARGE(ROW($1:$600)*NOT(COUNTIF($A$1:A1, ROW($1:$600)-300)), RANDBETWEEN(1,600-ROW(A1)))-300

If this isn´t working, email me.

I am trying to generate a random sequence of 1-10 without duplicates, but I would like to separate the numbers into two parts such that the first 5 numbers are a random sequence of 1-5 and the last 5 numbers are a random sequence of 6-10. (So the random sequence would look like this for example: 3 2 4 1 5 / 9 8 6 10 7) Is this possible to do with a formula? Do you have any suggestions?

Thank you.

Amy,

I am afraid no, but it is possible with a custom function (vba).

Hey,

For some reason I'm having trouble transposing these formulas for my needs.

I need 2 sets of random numbers in a spreadsheet, if possible 0-9 (not 1-10)

One set starting in cell C2 and going across the columns to L2

The other set starting in B3 and going down to B12

Thanks!

I need two sets of 10 random numbers between 0 and 9 (included) if 0 isn't an option then 1-10 is ok...

cell ranges are C2:L2 and B3:B12

Any help would be much appreciated!

Actually make those cell ranges C3:L3 and B4:B14. Sorry just having trouble with this

Hello, how I can generate 100 random numbers from 11111 up to 55555, but the numbers must be unique and not contain (0,6,7,8,9)

Daniel,

I am not sure you can do that with an array formula. You need a custom function (vba).

Can you help me?

Thank you so much for this simple solution. It's been the final piece in creating a differentiated started for practicing mental calculations in maths. Here's the result: http://lttmaths.com/2014/02/16/differentiated-mental-calculation-strategy-starter/

Nyima,

I am happy you found it useful. I have made something similar before:

http://www.get-digital-help.com/2013/08/23/basic-mathematics-in-excel-for-school-children/

[…] produces calculations suitable for each strategy (and looking on the web for methods to produce unique random numbers in Microsoft Excel - apparently harder than it first sounds!) Also included is a MENTAL TEST SCORE SHEET to record […]

I would like to produce a list of random numbers (non repeating). the number needs to be 5 digits. The number cannot start with zero. So it can have all the digits 0 - 9 in it but cannot start with zero. How do I do this?

leslie,

Try this array formula in cell A2:

=LARGE(ROW($10000:$99999)*NOT(COUNTIF(

$A$1:A1, ROW($10000:$99999))), RANDBETWEEN(1,89999-ROW(A1)))Remember to adjust the bolded cell reference if you enter the array formula in another cell.

Oscar, thank you very much. I ended up entering the formula to generate 5,000 numbers ($1000:$5999) but it took forever. My computer is new with plenty of processing ability so does it make sense that this took a long time. Furthermore, once it was complete I copied the column from that spreadsheet to another one and did a paste special value and it took 2 hours to paste. At then end of it though I have the 5,000 numbers.

Hi, Excellent description on how to use Excell.

However I'm attempting to generate 8 unique random numbers along a row ranging from 1-45. I have tried using your combination of your reply to fajar and the generate values from a cell range however I do not understand how to convert it to go across (along the row) not down (down the column)

Any help would be greatly appreciated!

When copying the formula to cell A2 Excel reports an error in COUNTIF($A$1:A1) pointing to A1 as the source. Any ideas?

Thanks.

Patrick.

Patrick,

Enter the formula in cell A2, not in cell A1. Then copy cell A2 to the cells below.

Genius thanks. I did notice that when modifying to get 30 unique numbers from a range 1-32 the number 1 is rare. Still trying to get my head round this. Also if I type into other columns it regenerates the values.

Hello,

I created the barcodes using a font type, but when I cannot read it with my barcode scanner Motorola MC3090. I've also tried with others barcodes for example, with a notebook and It works for it. What am I doing wrong? I have to write down and additional formula?

Thanks a lot for your help

Need assistance with random number. I'm dealing with service tickets in an excel sheet to generate a report for demo data.

I need to use the =TIMEVALUE("2:00:00") or go off of the original ticket time in the row, for instance. 6/6/2014 1:02:00 AM.

I would need to explain via web session or phone call. I'd be willing to pay $$ if someone wanted to help me.

How do you extrapolate the formula to make a unique table of numbers?

Hi,

I am trying to use the "unique random text strings" to make random pairs of students in my classroom. However: I have downloaded the example file, and while it works perfectly when I open it (I press F9 and get the states randomly sorted again and again), whenever I access the formula I get the #N/A error. I am on Excel 2007, norwegian language pack. All I have to do is click with my mouse in the formula line (like if I wanted to edit the formula) and press enter, and I get error.

What is it that changes when I try to edit the formula?

Thx

edit: I'm an idiot.

Remember to use ctrl+shift+enter instead of just enter when editing array files...

Please let me know if you're looking for a author foor your blog.

You have some really gokod posts and I feel I would be a good

asset. If you ever want to take some of thhe load off,

I'd really like to write some material for your blog iin exchanbge for a link back

to mine.Please blasat me an emil if interested. Kudos!

Hi I am trying to figure out how to use the array formula you supplied in the excel Unique random text strings... My list is a bit longer but I am unable to make changes to the formula. I keep getting error when I would like to make the ROW to 100. Yours stops at 52.

I might need more coffee but I can't seem to figure this out... Can you help?

Thanks in advance. Jennifer

Thank you so much for your help!

Hi, i am wanting to make a table of riders for random gates to start a race. There is 8 riders per race in 8 lanes and am trying to avoid riders getting the same lane.

Thanks in advance

First Name- ratul

Last Name- roy

Date of birth - 19/02/1987

I want a automated calculation in excel which print VP19021987RRAA

RR- first word of first name(Ratul-R) and first word of last name (Roy-R)

19021987- date of birth

AA- I want that value will automatically change when the same value will come that is VP19021987RRAA to VP19021987RRAB

Please help me

[…] How to create a list of random unique numbers in excel … – Table of Contents. Generate unique random numbers; Generate unique random values from a cell range; Generate unique random numbers. Question: How do I create a random … […]

RAND() function in Excel makes Ctrl+y stop working. Why?

[…] How to create a list of random unique numbers in excel […]

Hi,

I have a cell, A1. And I want to use random number generator to get an array basead on the number inside A1.

So let's say A1 is 2. the array i want is 2, 2+1, 2+2, 2+3 spread between A2~A5 randomly.

Is that possible?