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

alk3yn

New Member
Joined
Jun 1, 2019
Messages
5
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:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]o[/TD]
[TD]p[/TD]
[TD]q[/TD]
[TD]r[/TD]
[/TR]
[TR]
[TD]s[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]t[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]u[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]v[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]w[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]z[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]aa[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ab[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for any help! =)
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
At least for me, you need more explanation about what the numbers and letters mean in your table.
 
Upvote 0
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?
 
Upvote 0
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..
 
Upvote 0
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!
 
Upvote 0
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.).
 
Upvote 0
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 =(
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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".

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,179
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top