Generating random question numbers for surveys or exams

gcastner — Fri, 02 Mar 2007 23:37:00 GMT

I was recently asked to create a random question number generator in Microsoft Excel. The requirements were to create random question numbers from 1 to 65 that did not repeat. Here is the solution I came up with but I would interested to hear of any better methods:

Sub GenerateQuestions()

' Use Column B to display the random question numbers
Range("B1").Select

'Number of questions
questionCount = 65
For counter = 1 To questionCount
    ' Generate random number
    ActiveCell.FormulaR1C1 = "=RANDBETWEEN(1,65)"
    questionsAllocated = counter - 1

    ' Check if question number already allocated
    For checkCounter = 1 To questionsAllocated
        If ActiveCell.Value = ActiveCell.Offset(-checkCounter, 0).Value Then
            ActiveCell.Value = ActiveCell.Value + 1
            If ActiveCell.Value = questionCount + 1 Then ActiveCell.Value = 1
            checkCounter = 0
        End If
    Next checkCounter

' Copy and Paste Values so that allocated question numbers don't regenerate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

' Go to next question
ActiveCell.Offset(1, 0).Select
Next counter 'Loop

End Sub

Text manipulation in Microsoft Excel

gcastner — Sun, 11 Feb 2007 20:48:00 GMT

Here are a number of simple but useful functions for manipulating text in Microsoft Excel. One potential use of these functions is to separate last and first name when they are stored in a single cell. An example Excel file is also attached.

This formula will return the first five characters from the left in cell A2:

=LEFT(A2,5)

This formula will return the total number of characters (including spaces) in cell A2:

=LEN(A2)

This formula will return the number of characters (including spaces) from the left until the first space in cell A2:

=FIND(" ",A2)

To find first names where first name and last name are separated by a space:

=LEFT(A2,FIND(" ",A2)-1)

To find last Names where first name and last name are separated by a space:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

Duck Bytes : Microsoft Excel

Generating random question numbers for surveys or exams

Text manipulation in Microsoft Excel