• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

All Combination of Numbers that Equal to a given Sum-NON VBA

V

Vishal Srivastava

Member
Dear All,
I want to create a formula that gives all possible combination of numbers that equals to given sum.
Let start with following values of cell A1:A8 in column "A".
50
45
50
45
40
42
40
10
Suppose our target value is 90, there would be some combination such as 50+40,45+45, 40+40+10, etc.

Can we write a formula(NON-VBA) to know all possible combination?

I know, It possible with VBA and also Solver could give the answer but Solver gives only first combination of values not all combinations.

Can anyone help me out?

Thanks in Advance.

CMA Vishal Srivastava
 
Faseeh said:
Vishal,

Please see if this helps
Click to expand...
Dear Faseeh,
Thanks for you formula but It will not work where we have list of different data and also have a total of some data.

e.g:
If in a company we have paid total amount of Rs. 56,000 for 250 bills of 'X' amounts but we don't know which bills have been paid.

So my point is to find possible bills that have been paid of 'X' amounts.


Thanks in Advance!

CMA Vishal Srivastava
 
Dear Vishal,

Sorry for not understanding it correctly, in that please discard my solution. I guess whoever replies will come up with a vba solution.
 
hi
i'm new but need your urgent assistance
i have a work sheet with training data of different individuals with different Id for the different years . First i want to have data of same year on different worksheets and of different using year.
secondly using the id i want different individual on different worksheets
thanks
 
How to write that formula of creating combination in VBA
As if am writing the same formula using range().formula=Formula then it giving error as 'Unable to set FormulaArray..' same error if am using range.formulaArray. Is there any solution as I want to use this using VBA only
 
Surekha said:
How to write that formula of creating combination in VBA
As if am writing the same formula using range().formula=Formula then it giving error as 'Unable to set FormulaArray..' same error if am using range.formulaArray. Is there any solution as I want to use this using VBA only
Click to expand...

Code: 
Sub ArrayFormula()
    With Sheet1.Range("C3:H10")
    .FormulaArray = _
    "=INT(MOD(MOD(SMALL(ABS(#REF!)+ROW(B2:INDEX(B:B,2^ROWS(B3:B10))),C2:H2),10^7)/2^(ROW(B3:B10)-ROW(B2)-1),2))"
    .Replace "#REF!", _
    "ROUND((MMULT(INT(MOD(ROW(B2:INDEX(B:B,2^ROWS(B3:B10)))/2^TRANSPOSE((ROW(B3:B10)-MIN(ROW(B3:B10)))),2)),B3:B10)-B12)*10^9,-7)"
    End With
End Sub
 
Hi Lori,
Thanks for your reply.
I tried this formula but it doesn't allow me to write it using vba, because array formula has limit of 255 characters in vba. so getting error of 'Unable to set Array property'..
 
Hi ,

This works :
Code: 
Sub ArrayFormula()
    With ActiveSheet.Range("C3:H10")
        s = "=INT(MOD(MOD(SMALL(ABS(ROUND((MMULT(INT(MOD(ROW(R2C2:INDEX(C2,2^ROWS(RC2:R[7]C2)))/2^TRANSPOSE((ROW(RC2:R[7]C2)-MIN(ROW(RC2:R[7]C2)))),2)),RC2:R[7]C2)-R[9]C2)*10^9,-7))+1111,R[-1]C:R[-1]C[5]),10^7)/2^(ROW(RC2:R[7]C2)-MIN(ROW(RC2:R[7]C2))),2))"
   
        Application.ReferenceStyle = xlR1C1

        .FormulaArray = s
   
        .Replace "1111", "ROW(R2C2:INDEX(C2,2^ROWS(RC2:R[7]C2)))", xlPart
   
        Application.ReferenceStyle = xlA1
    End With
End Sub
Narayan
 
Surekha - please try the code samples provided.

Both NarayanK's and my suggestion use the replace method to work around the formula length limitation.
 
You must log in or register to reply here.
Back
Top