## Sort a list in random order in excel

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

### Category: Random

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: […]Comments(102) Filed in category: Excel, Random, Unique values

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

Comments(6) Filed in category: Excel, Random

### Category: Sort values

Comments(81) Filed in category: Excel, Sort values

Sort dates within a date range using excel array formula

Question: I have a list of unsorted dates. I want to extract records between two dates and I want the […]Comments(23) Filed in category: Excel, Sort values

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 […]Comments(17) Filed in category: Excel, Sort values, Unique distinct values, User defined functions (udf), VBA

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 […]Comments(16) Filed in category: Excel, Sort values

Sort text cells alphabetically from two columns using excel array formula

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]Comments(13) Filed in category: Excel, Sort values

### 4 Responses to “Sort a list in random order 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

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)