Preventing duplicate random gen

[joe1joe1joe2]

I have a spreadsheet I'm using to select questions at random from a chapter in a question bank for a topic I'm studying. It picks the questions proportionately depending on how many questions are in each chapter. But sometimes it returns a duplicate question in a chapter, which isn't that useful.

Does anyone know how I can prevent this? (See attached)

[:) Sixthsense :)]

Rand() and RandBetween() both will produce duplicates

Go for someother UDF approach to eliminate the duplicates

[popipipo]

Take a look at this sample to get unique numbers

[joe1joe1joe2]

Thanks, I got it to work using the method popipipo described.

[Tony Valko]

Rand() and RandBetween() both will produce duplicates

In theory, it is possible for RAND() to generate duplicates.

However, in reality what are the odds of that happening?

[:) Sixthsense :)]

in reality what are the odds of that happening?

May be the below link answer your above quoted question I believe

I am sure you might have seen that thread when the question was posted and discussed since it has been asked in the Old Microsoft Discussion Group

https://groups.google.com/forum/#!to...ns/UJzzhwl8M-E

[Tony Valko]

Hmmm...

I don't recall that thread.

Which post answers the question?

I've asked this question in the MS MVP forum and no one knows for sure but we all agree that it's a very big number.

Consider this...

RAND returns a number with 15 digits. If we need 1000 RAND functions what are the odds that any 2 of those numbers will be exactly the same 15 digits?

Theoretically, it's possible but the odds of duplicates are very, very slim.

[:) Sixthsense :)]

After doing several testing I realised that what you have said is true

I was in an impression that Rand functions will always produce duplicate (I have not tested it earlier which I done now) because most of the members (even some legends) have already mentioned in many posts that it will create duplicate, so myself too blindly accepted / suggested the same not only with this thread in many of the other threads too... lol

I apologize for giving such a wrong statement and thanks a lot for the correction too

[Tony Valko]

No need to apologize.

Thanks for the feedback!