Need help with creating all the possible combinations from the table.

[alk3yn]

after researching..i couldnt really come up with much results which is why im posting here.
1] is it possible to make out combinations with the given data?
2] also, i have to make sure that when it is combined, the total =10 not more or less
Here's the data:

	a	b	c	d	e	f	g	h	i	o	p	q	r
s	1		1		1		2		1			1	1
t	2						2
u		1		1		1	1					2
v			1		1	1	1	1		2
w			1			1	1			3	1		1
x				1					4
y		1						1
z	1				1	1		1	1				1
aa	2	2			1			1		2		1	1
ab					1		1

<tbody>
</tbody>

Thanks in advance for any help! =)

 

[kweaver]

At least for me, you need more explanation about what the numbers and letters mean in your table.

 

[kweaver]

Does the 2 in the intersection of "s" and "g" mean sg should be listed twice?
The 3 in the intersection of "w" and "o" mean that wo should be listed three times?

 

[alk3yn]

At least for me, you need more explanation about what the numbers and letters mean in your table.

what i mean is, i need to make combinations which should include a total of 10 of a respective row with either other rows or coloumns. (a total of row/coloumn is to be taken, sorry i didnt mention it in the table)
so for example with row x, the possible outcomes are:
xbp as [x=5,b=4,p=1,total=10]
xcd as [x=5,c=3,d=2,total=10]
xfp as [x=5,f=4,p=1,total=10]
and so on..

 

[alk3yn]

Does the 2 in the intersection of "s" and "g" mean sg should be listed twice?
The 3 in the intersection of "w" and "o" mean that wo should be listed three times?

no, thats not what i meant..please refer to the example i just sent, thanks!

 

[kweaver]

Well, I have a routine that will give the combinations when there are TWO such as SD because S totals 8 and D totals 2.
Also, TA because T totals 4 and A is 6; and TI as well.
Also, UB, UF, UH, UQ, UR total 10 because U is 6 and you have 4 in B, F, H, Q, and R

But, I'm not sure about finding combinations of THREE values (or 4, etc.).

 

[alk3yn]

Well, I have a routine that will give the combinations when there are TWO such as SD because S totals 8 and D totals 2.
Also, TA because T totals 4 and A is 6; and TI as well.
Also, UB, UF, UH, UQ, UR total 10 because U is 6 and you have 4 in B, F, H, Q, and R

But, I'm not sure about finding combinations of THREE values (or 4, etc.).

guess i have to write all those 286 combinations by myself then =(

 

[cerfani]

your data can be referenced with the offset function... just use random function to choose random offsets and repeat until you get 10... not sure exactly what you want to do... be thorough when you explain... you are asking vague questions

 

[alk3yn]

what i mean is, i need to make combinations which should include a total of 10 of a respective row with either other rows or coloumns. (a total of row/coloumn is to be taken, sorry i didnt mention it in the table)
so for example with row x, the possible outcomes are:
xbp as [x=5,b=4,p=1,total=10]
xcd as [x=5,c=3,d=2,total=10]
xfp as [x=5,f=4,p=1,total=10]
and so on..

your data can be referenced with the offset function... just use random function to choose random offsets and repeat until you get 10... not sure exactly what you want to do... be thorough when you explain... you are asking vague questions

umm...as i said i just want to know all possible combinations with that data i gave
im really sorry for making myself not specific... i think i gave some examples of the type of outcomes there has to be, if otherwise there is something else i need to disclose..can you please tell what exactly u need to know?

 

[MickG]

Try this:-
NB:- The total combination for all 23 possibilities approx. 8 million, but because the chances of more that 5 numbers giving you a 10 are remote, I have only run the code for 6 numbers.

With your data in Sheet1 range "A1:N11", then try this code for the Sum of numbers for each letter showing in columns "P & Q" and the combinations of letter that add up to 10 in columns "S & T".

Option Explicit
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Ray()
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

[COLOR="Navy"]Sub[/COLOR] MG02Jun14
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Range("B1:N1")
    Dic(Dn.Value) = Application.Sum(Dn.Offset(1).Resize(10))
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Range("A2:A11")
    Dic(Dn.Value) = Application.Sum(Dn.Offset(, 1).Resize(, 13))
[COLOR="Navy"]Next[/COLOR] Dn
Sheets("Sheet1").Range("P1").Resize(Dic.Count, 2) = Application.Transpose(Array(Dic.keys, Dic.items))

[COLOR="Navy"]Dim[/COLOR] rRng [COLOR="Navy"]As[/COLOR] Range, p, n, nRay, w [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] vElements, lRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] vresult [COLOR="Navy"]As[/COLOR] Variant
nRay = Application.Transpose(Dic.keys)
c = 0
[COLOR="Navy"]For[/COLOR] n = 1 To 6
    vElements = Application.Transpose(nRay)
        ReDim vresult(1 To n)
            Call CombinationsNP(vElements, CInt(n), vresult, lRow, 1, 1)
[COLOR="Navy"]Next[/COLOR] n
Range("s1").Resize(c, 2) = Application.Transpose(Ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Sub[/COLOR] CombinationsNP(vElements [COLOR="Navy"]As[/COLOR] Variant, p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] vresult [COLOR="Navy"]As[/COLOR] Variant, lRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] iElement [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] iIndex [COLOR="Navy"]As[/COLOR] Integer)
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] oSum [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]For[/COLOR] i = iElement To UBound(vElements)
    vresult(iIndex) = vElements(i)
    [COLOR="Navy"]If[/COLOR] iIndex = p [COLOR="Navy"]Then[/COLOR]
        lRow = lRow + 1
        [COLOR="Navy"]For[/COLOR] n = 1 To UBound(vresult)
            Txt = Txt & IIf(Txt = "", vresult(n), ", " & vresult(n))
            oSum = oSum + Dic(vresult(n))
        [COLOR="Navy"]Next[/COLOR] n
        [COLOR="Navy"]If[/COLOR] oSum = 10 [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            ReDim Preserve Ray(1 To 2, 1 To c)
            Ray(1, c) = Txt: Ray(2, c) = oSum
        [COLOR="Navy"]End[/COLOR] If
            Txt = "": oSum = 0
    [COLOR="Navy"]Else[/COLOR]
        Call CombinationsNP(vElements, p, vresult, lRow, i + 1, iIndex + 1)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] i
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick