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

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