5 number combinations from a defined set

[shabbis]

Hi,

Im trying to generate a 5 row array of random combinations of numbers from a defined set. I want the array to change at will from lets say 5x10 to 5x30. Ive tried to use some of Myra's vba code but it will not work. For example column a will have the following numbers 5,12,37,45,89 etc and I want to paste an array formula in a matrix and have it spit out random combinations of those numbers. Please help

Thanks,

 

[shabbis]

Sorry a 5 column array instead of row, the number of rows in the array will need to be user identified

 

[shabbis]

Also I don't want the same number repeated in the individual 5 number combinations. Although, the same number can appear in any of the other 5 number combinations

 

[shg]

One way:

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Biggest Num​			Num1​	Num2​	Num3​	Num4​	Num5​
2​	90​			27​	46​	47​	51​	64​		D2:H2 and down: {=SMALL(aiLexToCombo(5, RANDBETWEEN(0, $A$6-1)) + 1, {1,2,3,4,5})}
3​	Balls Drawn​			12​	18​	30​	55​	59​
4​	5​			13​	44​	64​	69​	80​
5​	Combos​			6​	17​	27​	29​	52​
6​	43,949,268​	A6: =COMBIN(A2, A4)		26​	30​	77​	80​	84​
7​				8​	48​	63​	67​	86​
8​				19​	25​	37​	60​	72​
9​				7​	18​	44​	65​	75​
10​				1​	2​	16​	22​	89​

Function aiLexToCombo(ByVal m As Long, ByVal cNum As Long) As Long()
  ' shg 2009, 2013

  ' VBA or UDF

  ' Does the opposite of iComboToLex; i.e., given a lexical combination
  ' number, returns the combination.

  ' Because of the order in which the combinations are generated,
  ' the value of n can be calculated. For example,
  '   =aiLexToCombo(3,  0) returns {2,1,0} (the first of nC3 for any n)
  '   =aiLexToCombo(3,  9) returns {4,3,2} (the last of 5C3)
  '   =aiLexToCombo(3, 10) returns {5,1,0} (6C3 after exhausting 5C3)

  Dim ai()          As Long    ' combination array
  Dim n             As Long    ' n+1 is the number of elements in the universe
  Dim i             As Long
  Dim j             As Long

  If cNum < 0 Or m < 1 Then Exit Function

  ReDim ai(1 To m)

  ' Find the minimum value of n for this cNum
  n = m - 1
  Do
    n = n + 1
    j = nComb(n, m)
  Loop While j <= cNum

  For i = m To 1 Step -1
    Do
      n = n - 1
      j = nComb(n, i)
    Loop While j > cNum

    ai(m - i + 1) = n
    cNum = cNum - j
  Next i

  aiLexToCombo = ai
End Function

Function nComb(n As Long, m As Long) As Long
  ' UDF or VBA

  ' Like Combin(n, m) except returns 0 instead of #NUM! for m < 0 or n < m

  ' COMBIN(9,3) and many others don't return integers! See OhNo below.
  If m >= 0 And n >= m Then nComb = CLng(WorksheetFunction.Combin(n, m))
End Function

 

[shabbis]

Thx this is helpful, I would like to run this with say 50 numbers of my choosing in column A and have the combinations derived from that set. How would I accomplish this?

 

[shg]

Use a lookup table:

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Biggest Num​			Num1​	Num2​	Num3​	Num4​	Num5​
2​	16​	A2: =ROWS(A11:A26)		e​	l​	a​	t​	r​		D2:H2 and down: {=LOOKUP(aiLexToCombo(5, RANDBETWEEN(0, $A$6-1)), $A$11:$B$26)}
3​	Balls Drawn​			s​	l​	y​	o​	u​
4​	5​			h​	y​	o​	n​	u​
5​	Combos​			t​	g​	r​	y​	u​
6​	4,368​	A6: =COMBIN(A2, A4)		l​	g​	i​	y​	p​
7​				s​	g​	o​	c​	n​
8​				l​	g​	i​	p​	u​
9​				a​	h​	i​	y​	c​
10​	Value​	Symbol​		a​	g​	y​	o​	n​
11​	0​	u​
12​	1​	n​
13​	2​	c​
14​	3​	o​
15​	4​	p​
16​	5​	y​
17​	6​	r​
18​	7​	i​
19​	8​	g​
20​	9​	h​
21​	10​	t​
22​	11​	a​
23​	12​	b​
24​	13​	l​
25​	14​	e​
26​	15​	s​

 

[shg]

... or

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Biggest Num​			Num1​	Num2​	Num3​	Num4​	Num5​
2​	16​	A2: =ROWS(A9:A24)		b​	a​	h​	i​	r​
3​	Balls Drawn​			s​	h​	g​	y​	u​
4​	5​			l​	b​	g​	i​	c​
5​	Combos​			e​	b​	r​	p​	c​
6​	4,368​	A6: =COMBIN(A2, A4)		e​	l​	a​	g​	r​
7​				s​	e​	a​	i​	n​
8​	Symbol​			s​	a​	i​	r​	u​
9​	u​			a​	r​	y​	c​	u​
10​	n​			s​	h​	g​	y​	p​
11​	c​
12​	o​
13​	p​
14​	y​
15​	r​
16​	i​
17​	g​
18​	h​
19​	t​
20​	a​
21​	b​
22​	l​
23​	e​
24​	s​

 

[shabbis]

Ok great! thanks so much

 

[shg]

You're welcome.