Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel random sorting query
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 --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel random sorting query
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel random sorting query
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel random sorting query
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 -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel random sorting query
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)" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel random sorting query
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 --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel random sorting query
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? --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel random sorting query
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. -- hth Max ----------------------------------------- Please reply in thread Use xdemechanik <atyahoo<dotcom for email -------------------------------------------------------- "fingers" wrote in message ... 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? --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel random sorting query
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel random sorting query
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 --- Message posted from http://www.ExcelForum.com/ |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel random sorting query
You're welcome, Fingers!
Nice of you to feedback. cheers Max ----------------------------------------- Please reply in thread Use xdemechanik <atyahoo<dotcom for email -------------------------------------------------------- "fingers" wrote in message ... 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 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Random sorting with restrictions | Excel Worksheet Functions | |||
Sorting random Data created from a random formula | Excel Discussion (Misc queries) | |||
Data Sorting Query | Excel Worksheet Functions | |||
Random sorting of list | Excel Discussion (Misc queries) | |||
Basic Sorting query | Excel Discussion (Misc queries) |