+ Reply to Thread
Results 1 to 3 of 3

Create a unique random number

  1. #1
    Jack
    Guest

    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?

  2. #2
    Bondi
    Guest

    Re: Create a unique random number

    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


  3. #3
    sswilcox
    Guest

    Re: Create a unique random number

    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1