Excel random sorting query

  • Thread starter Thread starter fingers
  • Start date Start date
F

fingers

I am trying to generate 'x' discrete lists of 6 numbers. The 25 lists
must come from a selected list of 12 of numbers. ie
12 numbers => Excel takes 6 random numbers from the list to create 'x'
number of lists.
Can anyone suggest a way of doing this?
Thanks
 
In fact, you don't need programming to achieve your task. Let's try to see how to solve it using Excel's built-in worksheet functions.

For example, you got your list of 12 numbers in column A, in the range A1:A12.
Now, create your first list of 6 "randomly picked" numbers in column C, in the range C1 to C6.

In cell C1, type the following formula:
=INDEX(A$1:A$12,INT(INT((12 * RAND()) + 1)))

Then use the mouse the drag the formula down, up to cell C6.
If you want 25 columns of such "randomly picked" numbers, with C1:C6 selected, use the mouse to drag the cells across to fill 25 columns to the right.

Now, a final step, if you want to "fix" the content of the cells (the values of the cells change every time the worksheet recalculates), with the 25 columns selected, from the "Edit" menu, choose COPY, then choose "Paste Special", paste only the Values.

It should be quite fast to complete the above steps. But if you insist to do it in a VBA way, let me know.


----- fingers wrote: -----

I am trying to generate 'x' discrete lists of 6 numbers. The 25 lists
must come from a selected list of 12 of numbers. ie
12 numbers => Excel takes 6 random numbers from the list to create 'x'
number of lists.
Can anyone suggest a way of doing this?
Thanks
 
Sorry, I just made a mistake in the formula. It should be:
=INDEX(A$1:A$12,INT((12 * RAND()) + 1))

----- Edwin Tam (MS MVP) wrote: -----

In fact, you don't need programming to achieve your task. Let's try to see how to solve it using Excel's built-in worksheet functions.

For example, you got your list of 12 numbers in column A, in the range A1:A12.
Now, create your first list of 6 "randomly picked" numbers in column C, in the range C1 to C6.

In cell C1, type the following formula:
=INDEX(A$1:A$12,INT(INT((12 * RAND()) + 1)))

Then use the mouse the drag the formula down, up to cell C6.
If you want 25 columns of such "randomly picked" numbers, with C1:C6 selected, use the mouse to drag the cells across to fill 25 columns to the right.

Now, a final step, if you want to "fix" the content of the cells (the values of the cells change every time the worksheet recalculates), with the 25 columns selected, from the "Edit" menu, choose COPY, then choose "Paste Special", paste only the Values.

It should be quite fast to complete the above steps. But if you insist to do it in a VBA way, let me know.


----- fingers wrote: -----

I am trying to generate 'x' discrete lists of 6 numbers. The 25 lists
must come from a selected list of 12 of numbers. ie
12 numbers => Excel takes 6 random numbers from the list to create 'x'
number of lists.
Can anyone suggest a way of doing this?
Thanks
 
The VBA way to solve your problem is below. You may easily modify the values of the variables to suit your needs

For example you got the list of number in A1:A12. Select any cell on the same worksheet, for example, C1, and run the macro

Sub random_pick(
Dim row_number As Intege
Dim set_number As Intege
Dim source_range As Rang
Dim tmp1 As Integer, tmp2 As Intege

Set source_range = ActiveSheet.Range("A1:A12"
row_number =
set_number = 2

With Selection.Cells(1
For tmp1 = 0 To set_number -
For tmp2 = 0 To row_number -
.Offset(tmp2, tmp1).Value = source_range.Cells(Int((source_range.Cells.Count * Rnd() + 1))).Valu
Nex
Nex
End Wit
End Su

----- fingers wrote: ----

I am trying to generate 'x' discrete lists of 6 numbers. The 25 list
must come from a selected list of 12 of numbers. i
12 numbers => Excel takes 6 random numbers from the list to create 'x
number of lists
Can anyone suggest a way of doing this
Thank
 
This might work for you...put your list of 12 numbers in A1:A12 in any sheet
and the list will be generated in Range(D1:I25)

Sub GenerateList()
Dim R As Byte, C As Byte
For R = 1 To 25
For C = 4 To 9
Cells(R, C) = Cells(Int((12 * Rnd) + 1), 1).Value
Next C
Next R
End Sub

--
Regards,
Rocky McKinley


Edwin Tam (MS MVP) said:
In fact, you don't need programming to achieve your task. Let's try to see
Click to expand...
how to solve it using Excel's built-in worksheet functions.
For example, you got your list of 12 numbers in column A, in the range A1:A12.
Now, create your first list of 6 "randomly picked" numbers in column C, in the range C1 to C6.

In cell C1, type the following formula:
=INDEX(A$1:A$12,INT(INT((12 * RAND()) + 1)))

Then use the mouse the drag the formula down, up to cell C6.
If you want 25 columns of such "randomly picked" numbers, with C1:C6
Click to expand...
selected, use the mouse to drag the cells across to fill 25 columns to the
right.
Now, a final step, if you want to "fix" the content of the cells (the
Click to expand...
values of the cells change every time the worksheet recalculates), with the
25 columns selected, from the "Edit" menu, choose COPY, then choose "Paste
Special", paste only the Values.
It should be quite fast to complete the above steps. But if you insist to
Click to expand...
do it in a VBA way, let me know.
 
ms mvp
no need for vb. only a little work involved in pasting the special
values each time.
thanks alot for your quick reply.
have a great new year mate
fingers:) :D :cool:
 
Rocky, Edwin,
the list are generating OK but there are duplicated numbers in most of
the lists. How can I modify the script to ensure each list is
different?
:confused:
 
For an approximate* but "fun" solution
using only worksheet functions,
try playing around with this simple set-up

Fill in A1:A12, the numbers 1 - 12
Put in B1:B12, your 12 numbers
Name the range A1:B12 as say: MyTable

Put in C1: =RAND(), copy down C1:C12

Name the range C1:C12 as say: TBL1

Select D1:D12
Put in the formula bar: =RANK(TBL1,TBL1)
Hold down Ctrl + Shift keys, press Enter
(It's an array formula)

Done correctly, Excel will wrap curly braces around
the formula, viz: {=RANK(TBL1,TBL1)}

(In D1:D12 will be a random shuffle of the numbers 1 - 12
without repeats)

Put in E1: =VLOOKUP(D1,MyTable,2,0)
Copy down to E12

Put in G1: =INDIRECT("E"&6*ROW()-10+COLUMN()-2)
Copy G1 across to L1, then down one row to L2

In G1:L2 will be 2 random unique sets
of 6 numbers from your 12 numbers in B1:B12

Select G1:L2 and
Copy > Paste Special > Values somewhere else

Press F9 to re-generate another 2 sets in G1:L2
and freeze the values somewhere else

Repeat above until you get the desired number of sets

*Note: It's only an approximation because
there's altogether a total of 924 unique sets of 6 numbers
which can be formed from your set of 12 numbers,
i.e. =COMBIN(12,6) returns 924

There's still the possibility (albeit quite remote)
that the successive sets of 6 numbers generated
with each press of F9 could actually be duplicates
of earlier sets generated, from amongst the total of 924 uniques

But the chances of this happening from the
12 - 13 re-generations (to get your 25 sets) may actually
turn out to be quite remote.
 
Unique values? No problem with VBA!

See the following extended macro. Note the first two lines "Option Explicit" and "Option Base 1" should be placed on top of the module.

Also, because you want unique numbers, the number of rows in a set should not be larger than the number of rows in the original number source. The macro will report an error and will abort automatically.

Again, it should be quite easy to modify the macro.

'----------------------------------------------------------------------------
Option Explicit
Option Base 1

Sub random_pick()
Dim row_number As Integer, set_number As Integer
Dim source_range As Range
Dim tmp1 As Integer, tmp2 As Integer, tmp3 As Integer
Dim tmp4
Dim unique_check As Boolean
Dim result_array()

Set source_range = ActiveSheet.Range("A1:A12")
row_number = 6
set_number = 25

If row_number > source_range.Rows.Count Then
MsgBox ("Error!")
Exit Sub
End If

ReDim result_array(row_number, 1)

With Selection.Cells(1)
For tmp1 = 0 To set_number - 1
For tmp2 = 1 To row_number
Do
tmp4 = source_range.Cells(Int((source_range.Cells.Count * Rnd() + 1))).Value
unique_check = True
For tmp3 = 1 To tmp2
If result_array(tmp3, 1) = tmp4 Then
unique_check = False
Exit For
End If
Next
Loop Until unique_check = True
result_array(tmp2, 1) = tmp4
Next
.Offset(0, tmp1).Resize(row_number, 1).Value = result_array
Next
End With
End Sub

'----------------------------------------------------------------------------

The whole idea of this macro is:
1) Draw random numbers in an array, 6 rows and 1 column in dimensions. (Imagine, the array is a space in the memory of the computer. This makes processing lightning fast.)
2) Whenever a new number is picked, compare it with all the previously drawn numbers in the array. If not unique, redraw.
3) After a set of 6 unique number is drawn, place it onto the spreadsheet.
 
Edwin,
PERFECT! Thanks heaps. Happy New Year to you.

Max,
your spin on the problem was one I did not think of. I have also
benefited and learnt from your idea. Thanks also to you.

Regards
Fingers
:D :D :D :D
 
Back
Top