52. Excel Tips - Random Sort in Excel
This tip shows you how to do a random sort of a list in Excel.
Suppose you have a list of people and you need a way to find out who goes first. Maybe you're the HR department of an investment bank and you need to decide quickly who to make redundant.
Here's a screen shot:
Here's the formula you would use:
=INDEX($A$2:$A$11,MATCH(SMALL(COUNTIF($B$2:$B$11,"<"&$B$2:$B$11)
+COUNT($B$2:$B$11),ROW()-ROW($A$2)+1),
COUNTIF($B$2:$B$11,"<"&$B$2:$B$11)+COUNT($B$2:$B$11),0))
.. you need to enter it as an array formula with the array the same size as the list.
Follow this link for an Excel tip on how to modify an array formula.
»
- Nick's blog
- Login or register to post comments
- 16389 reads
Random Sort in Excel
Love the site. Just an idea to shorten the formula for 'Random Sort in Excel'. If, as above, the names are in A2:A11, =RAND() in B2:B11, then a much shorter formula for C2 (to be dragged down for the rest), could be: "=OFFSET($A$2,RANK(B2,$B$2:$B$11)-1,0,1,1)".