How to scramble or shuffle text in excel

excelldummy

New Member
Joined
Sep 12, 2009
Messages
4
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!
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
ABCDEF
1
2set 1set 2set 3
3Q1S1Q1S2Q1S3Q1
4Q2S1Q2S2Q2S3Q2
5Q3S1Q3S2Q3S3Q3
6Q4S1Q4S2Q4S3Q4
7Q5S1Q5S2Q5S3Q5
8
9
10Question Set 1Question Set 2Question Set 3Question Set 4Question Set 5
11Q1S1Q1S1Q1S3Q1S1Q1S1Q1
12Q2S2Q2S3Q2S2Q2S2Q2S3Q2
13Q3S3Q3S3Q3S2Q3S3Q3S1Q3
14Q4S2Q4S3Q4S3Q4S2Q4S3Q4
15Q5S1Q5S3Q5S3Q5S3Q5S1Q5
Sheet1
Excel 2007
Cell Formulas
RangeFormula
B11=INDEX($B3:$D3,RANDBETWEEN(1,3))



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

TAPS_MikeDion

Well-known Member
Joined
Aug 14, 2009
Messages
524
Office Version
2011
Platform
MacOS
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,953
Messages
5,471,678
Members
406,778
Latest member
markusreyes2907

This Week's Hot Topics

Top