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

A

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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
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
kweaver said:
At least for me, you need more explanation about what the numbers and letters mean in your table.
Click to expand...

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
kweaver said:
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?
Click to expand...

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
kweaver said:
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.).
Click to expand...

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
alk3yn said:
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..
Click to expand...

cerfani said:
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
Click to expand...

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
You must log in or register to reply here.

Similar threads

G
  • Solved
Formula to make combination of elements given possible variables?
Replies
5
Views
249
felixstraube
F
C
  • Question Question
Allocation Across Months
Replies
3
Views
407
NickFru
N
K
  • Solved
Update to existing Move data macro
2
Replies
11
Views
725
Alex Blakenburg
A
K
  • Solved
Previously working very complex VBA now stopped
Replies
7
Views
510
Alex Blakenburg
A
E
  • Solved
Formula not working
Replies
2
Views
299
esmarques
E

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

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