Generate data in Excel using Macros?

Question

I need to create a table with the following structure:

 Applicant | Test 1 | Test 2 | Test 3 | Test 4 | Test 5 | Test 6 |
 1         |   A    |   C    |   D    |   E    |   F    |   B    |
 2         |   C    |   B    |   A    |   E    |   D    |   F    |
 3         |   C    |   A    |   F    |   E    |   B    |   D    |
 ....      |        |        |        |        |        |        |

Basically, test 1 - 6 can be any letter between A and F. I want a Macro (or some other method) by which I can generate this table, with 200 applicants, where the tests are completely randomised. Anyone know how to do this?

Answer 1

You don't need a macro. A single formula will do this: =CHAR(RANDBETWEEN(65, 70))

Answer 2

There are 720 permutations of 6 characters.

Although you could generate a random permutation by selecting one, then another from the remaining set etc., I think you'd be better off generating all 720 into a worksheet and then selecting 200 random rows from the worksheet.

There's code example to generate the permutations here: http://j-walk.com/ss/excel/tips/tip46.htm

then you can use randbetween() to select a row from those.

Answer 3

Since you say you don't want to generate the permutations separately, here is some VBA code that generates random perms for you. It's a bit brute-force-y though.

Option Explicit

Function candidateAlreadyInUse(r As Integer, cand As String) As Boolean

Dim col As Integer

candidateAlreadyInUse = False

For col = 1 To 6
    If ActiveSheet.Cells(r, col) = cand Then
        candidateAlreadyInUse = True
        Exit Function
    End If
Next col

End Function

Sub perm()

Dim row As Integer
Dim col As Integer
Dim candidate As String

For row = 1 To 10
    For col = 1 To 6
        candidate = Chr(65 + Int(Rnd() * 6))
        While candidateAlreadyInUse(row, candidate)
            candidate = Chr(65 + Int(Rnd() * 6))
        Wend
        ActiveSheet.Cells(row, col) = candidate
    Next col
Next row

End Sub

Answer 4

Just for kicks, I quickly implemented a Shuffle in VBA, which will create a random permutation of the letters A,B,C,D,E,F, using Fisher-Yates - pass it an array, and it will shuffle it:

Public Sub Shuffle(ByRef items() As String)

  Dim i, j As Integer
  Dim temp As String

  For i = UBound(items) To i = 1 Step -1
    j = Rnd * i
    temp = items(j)
    items(j) = items(i)
    items(i) = temp
  Next

End Sub

The following code creates an array A,B,C,D,E,F, generates 200 random permutations, and writes them row by row to the Active Sheet:

Public Sub WriteTests()

  Dim tests(0 To 5) As String

  tests(0) = "A"
  tests(1) = "B"
  tests(2) = "C"
  tests(3) = "D"
  tests(4) = "E"
  tests(5) = "F"

  Dim row, col As Integer
  Dim mySheet As Worksheet
  Set mySheet = ActiveWorkbook.ActiveSheet
  For row = 1 To 200
    Shuffle tests
    For col = 1 To 6
      mySheet.Cells(row, col).Value2 = tests(col - 1)
    Next
  Next

End Sub