RANDBETWEEN with no duplicates

[dparteka]

I'm pretty sure it is possible to use RANDBETWEEN without getting duplicate numbers, but I have not been able to figure it out, any help will be greatly appreciated... I've attached a spreadsheet.

 

[p45cal]

In attached 2 sheets with a different way on each.

 

[Villalobos]

Is the VBA allowed for you? If so then here is another option, just press the command button and follow the instructions.

Check it out.

 

[p45cal]

…in that case, a little speeding up of Villalobos' macro.
Check out speed difference when you try 1000 or 10000 values.
2 buttons in the attached.

 

[xlstime]

Hi Dparteka,

You can achieve this only with help of helper column or VBA.

try with helper column

cell A1 = =RAND() and drag till A10
cell B1 = =RANK(A1,$A$1:$A$10,0) and dran till B10

You will get a list of unique random number.

I'm pretty sure it is possible to use RANDBETWEEN without getting duplicate numbers, but I have not been able to figure it out, any help will be greatly appreciated... I've attached a spreadsheet.

 

[bobhc]

This link has a lot of information that you may fin of use.

http://www.tushar-mehta.com/excel/newsgroups/rand_selection/index.html

.

 

[dparteka]

WOW, I'm blown away, talk about a cornucopia of information and choices.

Villalobos… the InputBox, this is off the charts awesome, what a great feature and idea. It just occurred to me that what you’ve done will allow me to do much more than I had started out to do, including picking my next lottery numbers, if I win big we can all retire early.

P45cal… to drive a car from New York to San Francisco takes about 43 hours unless you take the Space Shuttle which makes the trip in 10 minutes at 17,500mph. What you’ve done is convert the car into a Space Shuttle, the numbers appear instantaneously, very cool.

xltime… “helper column”, I had to do a little research because this is a term I am not familiar with. I get it now, thanks for your input.

bobhc… thank you for the link, lots of good stuff there.

Thanks to all… hanging with you guys has made me a little bit smarter.