Create a unique random number

I have 40 questions which i want arranging into 6 sets of 15 questions. To do
this i thought i would randomly generate the numbers 1 to 40 15 times and
then repeat this. The problem i am getting is i keep on getting some numbers
repeating in the sequence. I am using the following code - =RAND()*(40-1)+1.
This works but i can't have the same question appearing twice.

Anyone know how i can adapt this to suit my needs?

Hi,
Here are two links that have some answers

http://www.iansharpe.com/art_excel_random.php

http://www.mcgimpsey.com/excel/udfs/...noreplace.html

Regards,
Bondi

I use the RANK(CR:CR) on my list of RAND() numbers in order to
determine each number's relative value and avoid duplicates. So I would
go about it like this:
- enter all questions in cells C1:C40
- in column B, enter a simple RAND() formula, copied down to B40
- in column A, enter the formula RANK(B$1:B$40), copied down to A40

Now, on another worksheet of the workbook, I would prepare my end-use
document with the questions. In column A type the numbers 1-15 next to
the appropriate cell in column B where the questions are to appear. In
the corresponding cell in column B (assumes B1 for this example), type
=VLOOKUP(B1,[nameofotherworksheet]!A$1:A$40,3,FALSE).
Copy and paste that formula into every cell in column B where you want
a question to appear.
Excel will look-up the question number from column A and pull the
corresponding randomly numbered question from your databank. Every time
you hit F9 it will recalculate and present a new, unduplicated set of
questions.
There is no guarantee, however, that all of your questions will be
used. It's the luck of the draw.