SubscribeSorting a list of items in random order in excel – using formulas
In shuffling a list of items in excel I have described the technique of using random numbers generated by RAND() to sort a list of items. The technique had one disadvantage though, every time you need to reshuffle the list you have to press F9 to recalculate the rand() and then go to menu > data > sort and sort the data again based on the new random numbers.
Here is a better technique that needs one key stroke to reshuffle the list of items (sorting the list in random order every time you press the key F9):
- Insert 2 columns to the left of the list of items you want to shuffle
- In the first column fill a series of numbers starting with 1
- In the next column fill RAND() formula
- Now, next to the list of items you want to sort in random order, we will use both VLOOKUP() and SMALL() excel formulas to fetch items in random order. See the formula below:
The SMALL() excel spreadsheet formula is used to sort a list of numbers and fetch nth smallest number in a given list.
- When you want to reshuffle the order, just hit F9
More sorting: Sort text / tables from left to right along columns
|
Trackbacks & Pingbacks
- Pingback by Deal or No Deal - Download and Play in Excel - Popular TV Game Show | Pointy Haired Dilbert - Chandoo.org on October 3, 2008 @ 8:16 pm
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
Adjust width of Column Shortcut Alt,...
Have you tried the =Sort() function in Google spreadsheets?
I know, believe me, how much fun it is to use all sorts of cool combinations of functions to achieve these little tasks – but Sort() doesn’t take away that fun – it just adds another tool to the box … and then check out Unique() and Filter()…. Can’t wait to see what you do with those
@JR … Yeah, the SORT(), UNIQUE() and FILTER() are pretty powerful functions and I use them to speed up work whenever I can. I wish MS would add these as basic functions in the spreadsheet and use continue() mechanism to handle arrays instead of those complex array formulas.
I Have the list of codes which are of 9 digits each and values of that codes, now I wish to make the sum of all those values against codes having “1″ or “2″ as its 3rd digit out of nine. please help me in this regard. will using if formula help?
@Shilpa… you can use sumif() formula to do this.
First next to the codes insert a blank column and fill it with the formula to fetch the 3rd digit of the code. you can use mid(code,3,1) to do this.
Then you can use sumif() on this column and values column to conditionally sum values when the code is 1 and 2.
Read more on sumif() here: http://chandoo.org/wp/2008/06/09/what-the-if-learn-6-cool-things-you-can-do-with-excel-if-functions/