Random Numbers With No Duplicates

I want to set up a column of numbers that will be random without any repeating numbers.

Example: If I set up for 1,000 rows, I would like the random numbers to be 1 to 1,000, but the catch would be to have no duplicates in the column.

It has been several years since I worked with Excel and I am sure this can be done with a macro, but I am not sure how to go about it right now.

Originally Posted by Cheeky Charlie

Would it be fair to say you want specified (not random) numbers in a random order?

If not, perhaps you could upload an example, showing what you're looking for, if so:
You could put the numbers 1 to 1000 in a column, then =rand() copied down alongside, then select the top rand() and click sort. Every sort will recalculate the rand()s and reorder the 1-1000

hth

You phrased it much better that I did.

I want a list of specified numbers to be in a random order.

Thank you for helping me to clarify.

The solution I used was to put my numbers in Column A, put a Rand() function in Col B, and then put a rank function in Col C.

Col B Formula:

Please Login or Register  to view this content.

Col C Formula

Please Login or Register  to view this content.

And then copy formulas cells B1 & C1 down as far as cells in Col A have a value.

This works fine if the list of numbers is from 1 to n...

Originally Posted by Cheeky Charlie

What an interesting variation, whatever flicks your switches, but if you just had two columns as I described, you could specify non-consecutive-integer values in column a and still randomly order them - a more generic solution (with fewer formulae)
CC

I already discoverd that if I later decided to have more, or non-sequential numbers, I would need to use your solution.

Thanks for the help.

They need not be numbers at all. It can be a list of cities, people, towns with only one vowel, ...

It's just a way to randomly order things, Dennis.