Randomize and mix table for study purpose

Matt Gunawan · Oct 10, 2014

Hi, My name is Matt and I have 10 tables of words, Each table contain 500 words.
I would like to mix them up in random order.
I have searching for 2 weeks and I can't found the solution for my problem.
I am a teacher and this is for study purpose. Any solution will be appreciated.

A	B	C	D-I	J
A1	B1	C1	..	J1
A2	B2	C2	..	J2
A3	B3	C3	..	J3
..	..	..	..	..
A500	B500	C500	..	J500

All the tables should randomized and mixed separate with comma (,)
Example solution : F21, B478, J112, D72, G91, A78, E387, I275, C218, H176
It's double randomization, randomize the table and randomize the result.
This is very important for my study. And I hope I can find the answer here.
Any help will be appreciated.

Thank you.

Matt

Matt Gunawan · Oct 14, 2014

Dear Rick, it is very helpful. Thank you very much. I really appreciate your help. I have more sample to randomize which is using same method. But the separator is " . " not " , " and the column are 4 and 8. I try to modify the the macro for 4 columns samples like this; I change "J500" with "D500" and ", " with ". " and "Select ten random values, one from each column" with "Select four random values, one from each column" but it doesn't works. Any help? Thank you.

Rick Rothstein said:

Here is a macro that will do all the work (you won't need any helper cells); just assign the appropriate values to the three Const statements and run when needed...

Code:

Sub TenRandomValues()
  Dim R As Long, C As Long, RandomIndex As Long
  Dim TenRandomValues As String
  Dim Temp As Variant, Arr As Variant, Answers As Variant
  Const Delimiter As String = ", "
  Const Addr As String = "A1:J500"
  Const OutputCell As String = "L2"
  '  Randomize each column of 500 values
  Arr = Range(Addr)
  For C = 1 To UBound(Arr, 2)
    For R = UBound(Arr) To 1 Step -1
      RandomIndex = Int(WorksheetFunction.RandBetween(1, R))
      Temp = Arr(RandomIndex, C)
      Arr(RandomIndex, C) = Arr(R, C)
      Arr(R, C) = Temp
    Next
  Next
  Range(Addr) = Arr
  '  Select ten random values, one from each column
  ReDim Answers(1 To UBound(Arr, 2))
  For C = 1 To UBound(Arr, 2)
    Answers(C) = Arr(WorksheetFunction.RandBetween(1, UBound(Arr)), C)
  Next
  '  Randomize their order and display them
  For C = UBound(Arr, 2) To 1 Step -1
    RandomIndex = Int(C * Rnd + 1)
    Temp = Answers(RandomIndex)
    Answers(RandomIndex) = Answers(C)
    Answers(C) = Temp
  Next
  Range(OutputCell).Value = Join(Answers, Delimiter)
End Sub

Big Don · Oct 20, 2014

Matt...

Here's a function you can use. It has 3 parameters
* The range of the cells to use as input
* The separator to use. This is optional. If left blank the contents of the input cells will be continuous.
* How many of the cells from the range to use. This is also optional. If left blank it will include all cells.

Function RandomConcat(rng As Range, Optional sep As String, Optional NumToUse As Double)
Const ArrLBound = 1
Dim ArrUBound As Double
Dim UseNum As Double
Dim arr() As StrWVal
Dim sorttemp As StrWVal
Dim outtemp As String
Dim i As Double
Dim j As Double
Dim cel As Range
ArrUBound = rng.Cells.Count
ReDim arr(ArrLBound To ArrUBound)
If NumToUse = 0 Then
UseNum = ArrUBound
Else
UseNum = NumToUse
End If
i = 0
For Each cel In rng
i = i + 1
arr(i).str = cel.Value2
arr(i).val = Int((UBound(arr) - LBound(arr) + 1) * Rnd + LBound(arr))
Next cel
Dim lngMin As Long
Dim lngMax As Long
lngMin = LBound(arr)
lngMax = UBound(arr)
For i = ArrLBound To ArrUBound - 1
For j = i + 1 To ArrUBound
If arr(i).val > arr(j).val Then
sorttemp = arr(i)
arr(i) = arr(j)
arr(j) = sorttemp
End If
Next j
Next i
For i = ArrLBound To UseNum
outtemp = outtemp & IIf(Len(outtemp) > 0, sep, "") & arr(i).str
Next i
RandomConcat = outtemp
End Function

Matt Gunawan · Oct 20, 2014

Thank you Bid Don. I really appreciate your help. But I don't really understand. Would you please explain more. Thank you.

Big Don said:
Matt...

Here's a function you can use. It has 3 parameters
* The range of the cells to use as input
* The separator to use. This is optional. If left blank the contents of the input cells will be continuous.
* How many of the cells from the range to use. This is also optional. If left blank it will include all cells.

Big Don · Oct 21, 2014

sorry for not being clearer.

To install the code follow Rick's instructions to this point

HOW TO INSTALL MACROsFunctions
------------------------------------
If you are new to macros and functions , they are easy to install and use. To install it, simply press ALT+F11 to go into the <acronym title="vBulletin" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VB</acronym> editor and, once there, click Insert/Module on its menu bar, then copy/paste the code I posted in my earlier message into the code window that just opened up. That's it.... you are done. (Thanks Rick!)

Now, when you go back to the worksheet it's just like entering any other function
1. type into a cell "=RandomConcat("
2 then just like a SUM() function, you can select the range of cells with the words to concatenate
3. Once the range is selected, type in a comma
4. Then, surrounded by quotes, what you want to use to separate the words, e.g. ", " would put a comma-space between them
5. If you want all of the words in the range, just close the parentheses.
6. If you want only some of the words in the concatenation (say you have a list of 8 and you only want 6) then before closing the parentheses type another comma and the number.

Matt Gunawan · Oct 22, 2014

Thank you very much Big Don. I really appreciate your help. Your explanation is very clear. I owe you Big Done. Have a nice day.

Randomize and mix table for study purpose

Matt Gunawan

New Member

Matt Gunawan

New Member

Excel Facts

Big Don

New Member

Matt Gunawan

New Member

Big Don

New Member

Matt Gunawan

New Member

Similar threads

Forum statistics

Share this page

Randomize and mix table for study purpose

Matt Gunawan

New Member

Matt Gunawan

New Member

Excel Facts

Big Don

New Member

Matt Gunawan

New Member

Big Don

New Member

Matt Gunawan

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock