Randomizing a range

**GUERCHARD** · 09-07-2012, 04:23 AM

how do I randomize a range of numbers. eg. column A contains no's 1-50, which i want to arrange in column B in random order, then column C, et cetera. Is there perhaps a formula?

**Spencer101** · 09-07-2012, 04:37 AM

Hi,

Have a look at the attached rough example of one way of doing this.

Column A uses RANDBETWEEN to generate random numbers between 1 and 50.
Problem with that is there could be duplicates.
So Column B takes care of that.

Of course, your real data may not just be 1-50 so it may have to be adapted slightly to accomplish what you're after, but it certainly can be done.

Happy to help further if you think this might help you out.

**GUERCHARD** · 09-07-2012, 05:44 AM

Thanks for the example, but this only works up to column C, after which every alternative number range (column) is a repeat of the previous columns. I literally just want the results in column B to be rearranged randomly in column C, and the results in column C to be rearranged randomly in column D, and so forth.

**Spencer101** · 09-07-2012, 05:49 AM

Apologies, I must have missed that last bit about needing it to continue column by column.

Are the values you want randomised actually 1-50 or are they something else?

**GUERCHARD** · 09-07-2012, 05:58 AM

They are actually 1 - 50. I have used =RAND() for column A, then the =RANK.. in column B to remove duplicates, now I just want to rearrange those results in column B in random order in column C, and then the results in column C also arranged randomly in column D, without a repeat of order from previous columns, et cetera.
I really appreciate the response.

**Spencer101** · 09-07-2012, 06:09 AM

How many columns of random 1-50's do you need? Would a hidden sheet in your workbook be a workable solution?

**Spencer101** · 09-07-2012, 06:17 AM

How about like this (see attachment V2)?

Sheet1 generates the random numbers (possibly with duplicates in columns) the same as column A on the first version did.

Shhet2 then makes makes them unique in the same way that column B did in the first version.

You can then hide Sheet1 and it's all done...