Excel-Filter - How do I randomly reorder an array?

May 13, 2008 11:24 AM

Someone asked me if I could use Excel to take a table of information and put it into a new table randomly. Without thinking about it too hard I said "Yeah, I bet I could do that." Turns out I spoke too soon.

Assume a 10 X 10 array, filled with data (for my test runs, I've been using the cell addresses as the data, so A1 has "A1" in it, B2 has "B2", etc.) The task is to populate a new 10 X 10 array with the same data, but in random order.

I'm not having any trouble with the randomness part, I've got a couple of solutions working for generating random cell references. I am having trouble making sure that every reference is generated at least once and only once. Thinking about it another way, I want to generate the numbers 1-100 in random order without any number repeating.

I have come up with a really cludgy solution where I generate enough random numbers to make sure that every number comes up at least once and then filter that list to remove repeats. (thanks to this thread)

But, I was hoping for something a little more elegant that that. I'd also like it to work automatically and I haven't been able to create a Macro that will automate that filtering operation. Oh, and I also don't know how many data elements will be in the table that needs to be randomized, and, while stats was a long time ago, I bet I'd need a lot of random numbers to be sure of generating every number from 1-1000, for example.

So, any ideas?

posted by qldaddy to computers & internet (7 comments total)

I don't know Excel well, and it doesn't seem like it lets you specify custom sorting functions, which is how I'd do this in code. But this page (scroll down) has a technique for doing something similar — in your case, you'd stick your 10 X 10 array into a 1 X 100 array, then populate an adjacent column with random numbers using RAND(), then sort on that column, then split your 1 X 100 array back into a 10 X 10 array.
posted by enn at 11:43 AM on May 13, 2008

The only thing I can think of is:

Let's say you have a list of 20 references.

1) Create a list/array/whatever consisting of the numbers 1, 2, 3, ... 20.

2) Find the number of elements in that list, call it L.

3) Randomly pick a number between 1 and L (or 0 and L-1, if you've got a zero-referenced array), called X.

4) Pick the Xth number in that array - use that.

5) Delete the Xth number from the array in Step 1.

6) Go to step 2 and repeat!

This way, your list is ever-shrinking. On your first number, you'll have 20 options. When you're picking your 18th number, you'll have only 3 options.
posted by adipocere at 11:44 AM on May 13, 2008

enn suggested exactly what I would do. Matrix->column. Sort on RAND. column->Matrix
posted by a robot made out of meat at 11:49 AM on May 13, 2008

I'd go with adipocere's method (see here for an example of how to remove an element from an array in VBA). If you try to do it by building the list from the ground up, it's going to get computationally expensive at an exponential rate. I just built a quick function to do it that way, and while it could spit out the numbers from 1-1000 randomly ordered in under a second, it took over a minute to do 4000.

On preview: My comment isn't a knock against the method enn found, just a personal preference.
posted by Doofus Magoo at 11:52 AM on May 13, 2008

It can be done without Macros if that makes life easier...

- Put an ID number (1 to x) against the table.

- Create a column of =rand() of the right length (i.e. x)

- Use =rank() against the rand() column... this will give you a column of randomised integers from 1 to x

- Then use lookup of the rank column against the ID of the information, to create a randomised list of the information.


MeMail me or email me (in my profile) for a worked example...
posted by saintsguy at 12:01 PM on May 13, 2008

Thanks all, I love Ask Mefi.

The last programming I did was for a class in high school (and we used Basic, Fortran, COBOL, and Assembly - so you knwo it was a while ago), so I'm going to pass on the VBasic solutions, although they sound neat.

saintsguy, I had not stumbled upon the rank function before - that looks pretty useful. I will see if I can puzzle out the combo myself. If not, you wil be hearing from me.
posted by qldaddy at 12:15 PM on May 13, 2008

For a quick random sequence of numbers, I just go here.
posted by bottlerocket at 2:45 PM on May 13, 2008