How can I randomize a set of numbers in excel.. is that even possible?

[ej2012]

I'm not to great with formulas in excel. Here's my problem and what I am trying to accomplish.. hopefully someone out there can help me. And if it's not possible, then it is what it is.

I have 10 rows. Each have 5 columns.

I want to take those 10 rows and their numbers and generate new combinations,
but I don't want the same number to be repeated in the new combination of 5 numbers twice.

For an example..

So if the number 26 is in those 10 rows somewhere, I need every combination that can be used with the number 26 only being used once, but using the other numbers from those 10 rows to produce a new set of 5 numbers.

Is that possible to do with excel or do I need something more? Or maybe it's not possible to do at all, maybe there are to many combinations to create?

actually this calculator I found online is close
http://www.randomizer.org/form.htm
but instead of a range of numbers, I need to be able to insert my own numbers for the calculator to chose from

 

[shg]

You have 50 cells. You want a random sequence of the numbers 1 to 50 to appear in them, or to see all of the combinations of 50 numbers chosen 5 at a time in successive rows, or something else?

 

[ej2012]

thanks for responding..

the cells all have numbers in them.. but those numbers could be dupes..

you might have 1 2 3 4 5 6 7 7 7 7 8 9 10 10 10 10 10 something like that.. the thing is even in that scenario you would never have a single row that has 5 numbers in this way

1 2 2 3 4

so to take all 50 cells and whatever numbers are in them.. then generate a new set of 5 numbers, but not repeating the same number in the new combination

hope that makes a little more sense

I think this is what I am looking for..

"to see all of the combinations of 50 numbers chosen 5 at a time in successive rows" just with the spin of never having the same number twice in a set of 5 that's generated

 

[shg]

Two moving parts here:

First, generate a list of the unique numbers; for your example, they would be 1 2 3 4 5 6 7 8 9 10.

Second, there is a workbook that will generate all combinations of N choose M in lexical order at http://www.box.net/shared/nqljsd3nlu, but the numbers are slightly different than what you require. The first combination of 10 choose 5, for example, is 4 3 2 1 0, and the last (there are 252) is 9 8 7 6 5. So the elements are always 0 through N-1. You can use columns to the right in the workbook to change those combinations to the numbers you want to use, either by just adding 1 to each number, or using a lookup table if you have gaps.

 

[ej2012]

ok so for the first part.. I created a list of the unique numbers
so there are 35 numbers here.. now to create all possible combinations in a set of 5.. using the workbook.. would i enter these numbers into the comb column on the left?
I think I am a bit confused on how to use the workbook to generate a new set of 5 numbers using just these numbers.

3
4
5
6
10
11
14
15
18
19
20
21
22
23
26
27
28
32
33
34
39
40
41
42
43
44
45
46
48
49
50
53
55
56
58

 

[shg]

Create a named range call myNums (or anything else you like).

Then:

       --B--- ---C---- D- E- F- G- H- I --J--- K --L--- --M--- --N--- --O--- --P--- Q --R---
   2        n      35                                                                       
   3        m       5                                                                       
   4   C(n,m) 324,632                                                                       
   5                                                                                        
   6                                                                                        
   7    Comb     n     C1 C2 C3 C4 C5   Helper   MyNum1 MyNum2 MyNum3 MyNum4 MyNum5   myNums
   8       1       35  4  3  2  1  0        1        10      6      5      4      3        3
   9       2       35  5  3  2  1  0        2        11      6      5      4      3        4
  10       3       35  5  4  2  1  0        3        11     10      5      4      3        5
  11       4       35  5  4  3  1  0        4        11     10      6      4      3        6
  12       5       35  5  4  3  2  0        5        11     10      6      5      3       10
  13       6       35  5  4  3  2  1        1        11     10      6      5      4       11
  14       7       35  6  3  2  1  0        2        14      6      5      4      3       14
  15       8       35  6  4  2  1  0        3        14     10      5      4      3       15
  16       9       35  6  4  3  1  0        4        14     10      6      4      3       18
  17      10       35  6  4  3  2  0        5        14     10      6      5      3       19
  18      11       35  6  4  3  2  1        2        14     10      6      5      4       20
  19      12       35  6  5  2  1  0        3        14     11      5      4      3       21
  20      13       35  6  5  3  1  0        4        14     11      6      4      3       22
  21      14       35  6  5  3  2  0        5        14     11      6      5      3       23
  22      15       35  6  5  3  2  1        3        14     11      6      5      4       26
  23      16       35  6  5  4  1  0        4        14     11     10      4      3       27
  24      17       35  6  5  4  2  0        5        14     11     10      5      3       28
  25      18       35  6  5  4  2  1        4        14     11     10      5      4       32
  26      19       35  6  5  4  3  0        5        14     11     10      6      3       33
  27      20       35  6  5  4  3  1        5        14     11     10      6      4       34
  28      21       35  6  5  4  3  2        1        14     11     10      6      5       39
  29      22       35  7  3  2  1  0        2        15      6      5      4      3       40
  30      23       35  7  4  2  1  0        3        15     10      5      4      3       41
  31      24       35  7  4  3  1  0        4        15     10      6      4      3       42
  32      25       35  7  4  3  2  0        5        15     10      6      5      3       43
  33      26       35  7  4  3  2  1        2        15     10      6      5      4       44
  34      27       35  7  5  2  1  0        3        15     11      5      4      3       45
  35      28       35  7  5  3  1  0        4        15     11      6      4      3       46
  36      29       35  7  5  3  2  0        5        15     11      6      5      3       48
  37      30       35  7  5  3  2  1        3        15     11      6      5      4       49
  38      31       35  7  5  4  1  0        4        15     11     10      4      3       50
  39      32       35  7  5  4  2  0        5        15     11     10      5      3       53
  40      33       35  7  5  4  2  1        4        15     11     10      5      4       55
  41      34       35  7  5  4  3  0        5        15     11     10      6      3       56
  42      35       35  7  5  4  3  1        5        15     11     10      6      4       58
  43      36       35  7  5  4  3  2        2        15     11     10      6      5         
  44      37       35  7  6  2  1  0        3        15     14      5      4      3

The data in D8:H8 and down is the output of the spreadsheet for combinations of 35 choose 5.

The formula in L8 and copied across is

=INDEX(myNums, D8+1)