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:
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?
Get 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
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 […]
Sort values category
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]
Excel categories
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
Paste image link to your comment.
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/how-to-return-multiple-values-using-vlookup-in-excel/
https://www.get-digital-help.com/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 Press with left mouse button on it and it updates all 3 rows into the numberical order based on row A
awall,
Read: Sort values in a table (vba)