How to scramble or shuffle text in excel

[excelldummy]

How to scramble text.

I have been trying to figure this out and i have had no luck. So far no one has been able to help me with this one. I will try to explain as best as possible.

I have have a quiz that i am tying to make, with multiple questions. But i want to make as many possibilities as possible.

Here is a basic example of a worksheet.

<table style="border-collapse: collapse; width: 192pt;" border="0" cellpadding="0" cellspacing="0" width="256"><col style="width: 48pt;" span="4" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">
</td> <td style="width: 48pt;" width="64">set 1</td> <td style="width: 48pt;" width="64">set 2</td> <td style="width: 48pt;" width="64">set 3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Q1</td> <td>S1Q1</td> <td>S2Q1</td> <td>S3Q1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Q2</td> <td>S1Q2</td> <td>S2Q2</td> <td>S3Q2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Q3</td> <td>S1Q3</td> <td>S2Q3</td> <td>S3Q3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Q4</td> <td>S1Q4</td> <td>S2Q4</td> <td>S3Q4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Q5</td> <td>S1Q5</td> <td>S2Q5</td> <td>S3Q5</td> </tr> </tbody></table>

I want to shuffle the questions but they need to be in order. I want to take the 3 sets of questions, and make hundreds of sets. The only problem is they all need to be in order.

EX:
S2Q1
S3Q2
S3Q3
S1Q4
S2Q5

I want to mix it up but keep the questions in order. I know that i can make 100's of possibilities. Please if anyone knows of a macro, or some code that will give me as many sets of questions as possible but keep them in order.
Thanks so anyone that helps!

 

[Richard Schollar]

Hi

You can do this using formulas. Assume you are using xl2007 or have the Analysis Toolpak installed if using xl2003 or below (via Tools>Add-ins menu) - this gives you access to the RANDBETWEEN function:

Excel Workbook
	A	B	C	D	E	F
1
2		set 1	set 2	set 3
3	Q1	S1Q1	S2Q1	S3Q1
4	Q2	S1Q2	S2Q2	S3Q2
5	Q3	S1Q3	S2Q3	S3Q3
6	Q4	S1Q4	S2Q4	S3Q4
7	Q5	S1Q5	S2Q5	S3Q5
8
9
10		Question Set 1	Question Set 2	Question Set 3	Question Set 4	Question Set 5
11	Q1	S1Q1	S1Q1	S3Q1	S1Q1	S1Q1
12	Q2	S2Q2	S3Q2	S2Q2	S2Q2	S3Q2
13	Q3	S3Q3	S3Q3	S2Q3	S3Q3	S1Q3
14	Q4	S2Q4	S3Q4	S3Q4	S2Q4	S3Q4
15	Q5	S1Q5	S3Q5	S3Q5	S3Q5	S1Q5
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B11	=INDEX($B3:$D3,RANDBETWEEN(1,3))

Formula in B11 is copied down/across. Note that this does not guarantee unique sets.

 

[TAPS_MikeDion]

Hi,
I'm not exactly sure if this is what you're looking for, but this will allow you to use any number of columns for your randomized questions, not just three, as stated in your post.

I threw in the MsgBox code so you could at least get an idea of what I was shooting for.

Sub Scramble()
 
    Dim x, RndCol, LastCol, LastRow
    LastRow = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
    LastCol = Sheets("sheet1").Cells(1, Columns.Count).End(xlToLeft).Column
 
    Randomize
 
    With Sheets("sheet1")
        For x = 1 To LastRow
            RndCol = Int((LastCol * Rnd) + 1)
            MsgBox "Question " & x & ": " & .Cells(x, RndCol), vbQuestion, "Question #: " & x
        Next x
    End With
 
End Sub