I have a long list of entries that I want to sort randomly. I highlighted a column, typed in =RAND(), and then pressed CTRL+SHIFT+ENTER, and voilà, my column was filled with random numbers. I then wanted to sort the column by smallest value to largest, to randomize my list. When I attempted this, I was given some sort of message saying that Excel cannot sort an "array."
So, I said to myself, Fine. I'll just make another column, paste those random numbers as values and continue on. Unfortunately, that did not solve the problem. Instead of telling me it cannot sort an array, it now pauses a bit and then erases all information in columns B and C and returns some different, yet still random numbers in column A. I even attempted to copy the data and repaste it into a another sheet to see if that would solve the problem, but it continues to do the same thing.
I did not find anything that really addressed this on the web or here in this forum, but I could have missed something, so I will continue to search for a solution, but if any of you Super User experts know why Excel does this and how to solve this problem, I'd love to hear what you have to say.
By the way, I am using Excel 2007, and I have used Excel's RAND function before and conducted successful sorts with this version of Excel, but I think I filled the cells the old-fashioned way -- grabbing the autofill crosshair and dragging it down through the cells. Those were for smaller sort jobs, however (maybe 100 or so records). I am now trying to do the same thing with 5,000 records and really don't want to use that method for all the obvious reasons.
Thanks in advance for any guidance you can give me on this.
Addendum: After posting this, I know why the cells in column B and C are blank. When I press CTRL+SHIFT+ENTER, Excel fills the entire column, but I only have 5,000 records. The easiest work around to this (off the top of my head) is to simply sort by column B or C, find the last entry and delete the rest of the rows. Having said that, what is the easiest way to fill a long list of cells with random numbers for Excel 2007? Is there some way that I can specify the exact cells I want it to fill with random numbers?