## Sort a list in random order in excel

*Article last updated on April 06, 2011*

### Overview

The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6.

### Array formula in C2:

**$C$1:C1**, List)<>COUNTIF(List, List)), RANDBETWEEN(1, SUM(--(COUNTIF(

**$C$1:C1**, List)<>COUNTIF(List, List)))))) + CTRL + SHIFT + ENTER.

### How to create an array formula

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

Copy cell C2 and paste it down as far as needed.

### How this formula works in cell C2

**Step 1 - Create an array of row numbers from the named range List**

=INDEX(List, LARGE(**(ROW(List)-MIN(ROW(List))+1)***(COUNTIF($C$1:C1, List)<>COUNTIF(List, List)), RANDBETWEEN(1, SUM(--(COUNTIF($C$1:C1, List)<>COUNTIF(List, List))))))

ROW(reference) returns the rownumber of a reference

(ROW(List)-MIN(ROW(List))+1)

becomes

(ROW(A2:A6)-MIN(ROW(A2:A6))+1)

becomes

{2;3;4;5;6}-MIN(ROW({2;3;4;5;6}))+1

becomes

{2;3;4;5;6}-2+1

becomes

{1;2;3;4;5}

**Step 2 - Check value frequency**

=INDEX(List, LARGE((ROW(List)-MIN(ROW(List))+1)***(COUNTIF($C$1:C1, List)<>COUNTIF(List, List))**, RANDBETWEEN(1, SUM(--(COUNTIF($C$1:C1, List)<>COUNTIF(List, List))))))

COUNTIF(range,criteria) counts the number of cells within a range that meet the given condition

(COUNTIF($C$1:C1, List)<>COUNTIF(List, List))

becomes

(COUNTIF("Random list", {"AA";"DD";"CC";"DD";"EE"})<>COUNTIF(List, List))

becomes

{0;0;0;0;0}<>COUNTIF({"AA";"DD";"CC";"DD";"EE"}, {"AA";"DD";"CC";"DD";"EE"})

becomes

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

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE}

**Step 3 - Calcultate randbetween arguments**

=INDEX(List, LARGE((ROW(List)-MIN(ROW(List))+1)*(COUNTIF($C$1:C1, List)<>COUNTIF(List, List)), **RANDBETWEEN(1, SUM(--(COUNTIF($C$1:C1, List)<>COUNTIF(List, List)))**)))

RANDBETWEEN(bottom, top) returns a random number between the numbers you specify.

RANDBETWEEN(1, SUM(--(COUNTIF($C$1:C1, List)<>COUNTIF(List, List)))))

becomes

RANDBETWEEN(1, SUM(--({0;0;0;0;0}<>{1;2;1;2;1}))))

becomes

RANDBETWEEN(1, SUM(--({TRUE; TRUE; TRUE; TRUE; TRUE}))))

becomes

RANDBETWEEN(1, SUM({1; 1; 1; 1; 1})))

becomes

RANDBETWEEN(1, 5)

and returns a random number between 1 and 5.

**Step 4 - Select a random row number**

=INDEX(List, LARGE((ROW(List)-MIN(ROW(List))+1)*(COUNTIF($C$1:C1, List)<>COUNTIF(List, List)), RANDBETWEEN(1, SUM(--(COUNTIF($C$1:C1, List)<>COUNTIF(List, List))))))

LARGE(array,k) returns the k-th largest row number in this data set.

becomes

=INDEX(List,LARGE({1;2;3;4;5}, 1))

becomes

=INDEX({"AA";"DD";"CC";"DD";"EE"}, LARGE({1;2;3;4;5}, 1))

and returns EE in cell C2. (random)

### How to customize the formula to your excel spreadsheet

Change the named ranges. If your random list starts at cell F2, change cell reference* ***C1:$C$1** in the above formula to $F$1:F1.

### Named ranges

*List *(A2:A6)

What is named ranges?

### Download excel sample file for this tutorial.

sort a list in random order.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**SUMPRODUCT(**array1, array2, **)**

Returns the sum of the products of the corresponding ranges or arrays

**COUNTIF(**range,criteria**)**

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

**LARGE(**array,k**)** returns the k-th largest row number in this data set.

**ROW(**reference**)** returns the rownumber of a reference

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

**RANDBETWEEN(**bottom, top**)**

Returns a random number between the numbers you specify

**ISERROR(**value)

Checks whether a value is an error and returns TRUE or FALSE

**MATCH(**lookup_value;lookup_array; [match_type]**)**

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

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

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

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

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]

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

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

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Sort dates within a date range

Question: I have a list of unsorted dates. I want to extract records between two dates and I want the […]

Extract unique distinct values from a filtered table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

Sort values in parallel (array formula)

Table of contents How to sort a table by Column 1 and then by Column 2 (array formula) How to […]

Lookup and return multiple values sorted in a custom order

Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to […]

### 4 Responses to “Sort a list in random order in excel”

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

Hello Oscar you look advance with excel, please help me. i can't write my problem here, please answer at _stackoverflow.com .

This is my question

_https://stackoverflow.com/questions/8034073/get-cell-value-from-random-rows-based-same-content-value

Alea,

are any of these posts answering your question?

https://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/

https://www.get-digital-help.com/2011/07/17/vlookup-across-multiple-sheets-in-excel/

Hey can you do the opposite of this - not random order but this is my situation.

i have 3 rows

A1 - Item Number

B1 - Description

C1 - Price

These will constantly be having new numbers put in. is there a way to have it when you add a number into the A coloum it will automatically sort into numerical order? Or can i create a button that after i input all my data click it and it updates all 3 rows into the numberical order based on row A

awall,

Read: Sort values in a table (vba)