2

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?

Lisa Beck
  • 203

1 Answers1

4

Whenever you press Ctrl+Shift+Enter, you're working with arrays, which have special rules. If you only want to fill a bunch of cells with a certain formula and avoid creating an array, enter your formula in one cell, then drag that cell's bottom-right grip to copy it to as many cells as you like.

Still, we have the problem that sorting the values causes a recalculation, and since the =RAND() formula is still in the cells, they'll be re-randomized and un-sorted. Therefore, you want to only generate the random numbers once, by converting the formulas to values once the randomness happens. Select the whole range of cells, then press Ctrl+C, Ctrl+V, Shift+F10, then just V. That copies, pastes, right-clicks, and sets the paste mode to "values only." It even works on the array you already created.

You can then sort the random now-static values as normal.

Ben N
  • 42,308