Here is a quick Monday tip for data junkies.
If you want to shuffle a list of numbers or cells in excel in random order you can do that with “data > sort” menu option of excel.
- First insert a column next to the list you want to shuffle and enter random numbers in each cell in using
=rand(). See below:
- Now select the entire list and sort it by the new column with random numbers. (Menu > data > sort)
- If you want a fresh set of shuffled list, just hit f9 (recalculates all formulas) and sort again
Like this? Also learn how to sort horizontally, fill only weekdays while auto-filling dates
50 Responses
Awesome! I’m glad someone else recognises the need to un-sort lists.
@Sam.. thanks 🙂
Another thing I do is add a third column with the numbers 1,2,3… and always sort with that included. That way I can return to the original order if I need to. (although in this case it is alphabetical).
@Tony: thanks, adding another column with original order helps, especially you are simulating random behavior and would like to get back to original order.
some of the uses for randomizing lists that I can think of:
– simulating games, card shuffles
– simulating gains over a very long period (10-20 years) where in you need to take a random set of market gains to see how the investments would grow.
Thank you so much. I am technically challenged, but you just gave me the power to help my 9-year old practice her written multiplication challenges at home. I want to give her pages with the tables in different orders, so she won’t memorize the answers. Thanks, again. Mimi
Same thing here, wanting the kids to practice mult or addition table from 0 to 10…
2 + … = 10
6 + … = 10
etc
Using the answer from Hui, you can use the following to regenerate the first number from the list 0 to 10:
– one (invisible) column C with formula =RAND() on each row
– first column A with the following formula =RANK.AVG(C1,C$1:C$11,1)-1
– copy and paste to every row below
That way, it recalculates automatically.
I don’t need any INDEX or OFFSET because my list uses numbers 0 to 10.
Thanks AndyK. I did not know about the RANK.AVG function. I used this which had the same effect:
= COUNTIF ($A$1:$A$10, “>=”&A1)
What this does is count the number of entries in A1:A10 that are greater than or equal to the value being ranked.
@Mini.. that is an excellent way to use the shuffle logic..
Thanks a lot !!!!! very useful for me…… thanks !!!!
excellent…thanks!
Thanks very much. I am preparing vocab for Gre and I need to shuffle these words. Thanks again.
I am a teacher and used this to help my students study spelling words. Thank you so much – you saved me hours of shuffling!
@Nikky, Hasanain, Laeeq and Ashlee.. you are welcome 🙂
I am trying to create about 40 bingo cards using words in excel is there anyway to create one card then shuffle or sort it to create the other cards
how can I create bingo cards in excel for a large group of people
pretty good advice 🙂 one of the simplest things, Random .. can be so mind boggling.. specially when a computer does it.
Sir, can you please write an application program (downloadable) that shuffles the list at the button click instead of we applying the rand() function and sorting it everytime? It will be very helpful. thanks 🙂
Great tip – just used it to allocate shirt numbers to the squad for a rugby tour.
@Peter.. awesome… 🙂
Awesome!!! I so needed this!
@Shwetha
Add a column of Rand() numbers =rand()
Add a column which Ranks the numbers =rank(Number, list,1)
Add a column which looks up the Ranks and extracts the appropriate data from original colums, use Index/Match or Offset/Match
Press F9 to re-sort
Have a look at an example at: http://rapidshare.com/files/427379371/Sorter.xls
I WANT TO SHUFFLE THINGS BASED ON SOME CRITERIA LIKE HERE 8 PERSONS A ,B, C,D ,E .F G,H & 8SKILLS ARE 1A,2A 3A,4A, 5A,6A,7A,8A HERE A,B ,G & H KNOWS ALL SKILLS, C & D DONT KNOW 3A, 5A, E & F DONT KNOW 2A & 6A THEN HOW SHUFFLE PERSONS BASED ON THEIR SKILLS without repeatation of same skill again…….THANK YOU….PLEASE HELP ME
I need a same soluation which Ashok said, any clues for it
BRILLIANT! Thank you! You just saved me TONS of work!
Thank you!
Very useful for decision tree creating in a university course called “Data Mining”!
Cheers 🙂
Brilliantly simple
Well thought out
Hi, I am a bit technically challenged but I got most of this. However…where did the list of numbers come from? Do you just make them up? Do they have to be in the format 0.123456? Does the =RAND() just go in the first cell of the new column?
I want to use this to randomise a list of tweets that I will put out over a period of time. The rest I understand and it looks perfect. Thanks!
i want make random 100 number in excel with
int(100*rand()) by i need number is sort
please help me
thanks
Genial! Thank you
Thank dude, I am was trying to create a semantically unpredictable sentences in a fixed syntactical structure. This definitely saved me hours..
The first time I saw this was at a tax certificate sale. Each property was shuffled by the numbers given to the people in the room.
If your number matched the property you got the certificate. Everyones assigned number shuffled on each property.
the best anyone could have done was to have their assigned number hit on every property.
there were more people in the room than properties.
Really fast and useful tip. Thank-you!
Good one!
One thing that I noticed though,
at least in Excel 2007, eevrytime you click/enter anything the random numbers change. So even when you sort the data the numbers change.
Thus just to be able to backtrack. I would make another column where I would copy the random numbers and paste only the values. Then I can delete the random number formula column and sort using random number values column.
Thanks.
Thanks a lot 🙂
Hi I’m trying to shuffle a 8 x 12 matrix, is that possible in excel?
Muito obrigado!
Thank you very much! really helped me a lot
I have a pool of 40 numbers I need all the possibilities of those 40 numbers in a 10 digit result without duplication. How can I get those results?
Hitting F9 just generates random numbers between 0 and 1; it doesn’t shuffle the words. It either doesn’t work in Excel 2013 or I couldn’t get it to work.
Correct, F9 regenerates the random numbers. But you still need to manually sort the columns afterwards.
I have 20 columns with 9 different names in each. I would like to shuffle these names every 12mins between all 20 colmuns. Can this be done?
Have a look at a technique I have made up shown here:
https://www.dropbox.com/s/9tzt8tum2mx6ira/New%20List%20of%20People.xlsx?dl=0
It uses a list of people and a random number
Then rank the random numbers to give a sorted list
Then use a Index/Match or VLookup to lookup the name associated with each number in your array
You can use the Row and Column numbers to get numbers 1 to 180
Hi,
I have a list with 20 items and I want to randomly move them many times, at least 100 times by ideally 1000 times – I am trying to do a statistical randomization. I wonder if it is possible to create a formula to do this.
Thank you very much. This is exactly what I wanted!!!
Please I need someone to help me shuffle numbers from 1,2,3… 90. I mean help Mr shuffle numbers from 1 to 90 and each line must contain 5 numbers… Example, if I am to shuffle numbers from 1 to 5 and each line must contain 3 numbers, I would have (1,2,3), (1,2,4), (1,2,5), (1,3,4), (1,3,5) (1,4,5) (2,3,4) (2,3,5) (2,4,5) (3,4,5)
@Francis
Have a read at:
https://sites.google.com/site/e90e50fx/home/combinatorics-using-excel-formulas-and-examples