• 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.

Combination: Marco for adding the cell value using combination of cells to give a specified value

Navi

Navi

Member
Hi All,
I am new to post a query but i have read many threads which was useful for me in day to day works. thanks for it. I am in need of your help here
I have a excel, where I have a list of Bill numbers (may be 1000) Column B and the amount in Column C.
I have few advance payments already been received. now the problem is I dont know which payment to be erased against which Bill.
Hence i need a help like, when i enter the value in one cell, Excel should keep adding the value in Column C (eg, c2+c3, c2+c4, c2+c5......c3+c4.....) and give me the result in separate sheet as what are the bill numbers that match the value and the amount. Excel should search all the combinations and provide output with results
can any help me with this.
kindly let me know if more details required.
Regards,
Navi
09884520062
 
Fair enought. I just need another detail: is the number of cell to sum fixed? I mean, given a goal, is always 2 cells that summed give the total?

What should happen if none of the combinations meets the goal? You want to get the closer value?
 
Thanks for your reply:)
Attaching a sample excel. Column A i have bill numbers and column B i have the amount.
when ever i enter a value in G2, excel should start taking combinations from B2 till end of the column (because data range may vary and go beyond B500 too).
It can have a combination of adding 2 cells or 3 cells or 4 cells. when the output matches with G2, resuslt should through the corresponding Bill numbers.:) let me know if more details required.

Regards,
Navi
 

Attachments

  • sample.xlsx
    9.6 KB · Views: 8
Hi Navi,

Do you think, this is logical.. just download the file.

Now.. Select any cell between E6:Ad31

It will highlight all the sum Bill's combination..
So.. how can you realize, this amount is for Bill # 17 + Bill # 19, and not for Bill # 12 + Bill # 21

Do you have any pre-planned logic...

PS: Dont forget to enable the macro. ;)
 

Attachments

  • sample.xlsm
    22.6 KB · Views: 9
As i do not have a formula yet...i'm not entitled to start a challenge :)
But, assuming that we have a Worksheet similar to Navi's and
- for each goal there is at least ONE 2-cell combo that meets the goal
- if more than one exists, we just want one

how would you solve this problem with a formula? :)
 
Hi Deb,

Thanks a lot for your help:)Sorry as I am based out of India, i checked your message just today morning. Hence delay.

I saw the attachment and its great work:)

I dont know if my expectation is more, just wanted to know if i enter a value in G2, will it highlight the matches which sums to it

Thanks a lot for your help.

Regards,
Navi
 
For instance, if the amount is $ 11555.6 then it would highlight Bill 17 & Bill 1 as the sum is by adding Bill 17+Bill 1

Regards,
Navi
 
will the combo box work? if we select the value in combo box, will it show the matching bill number with value? Am not getting thelogic, hence confused:(
 
Debraj said:
Hi Navi,

Do you think, this is logical.. just download the file.

Now.. Select any cell between E6:Ad31

It will highlight all the sum Bill's combination..
So.. how can you realize, this amount is for Bill # 17 + Bill # 19, and not for Bill # 12 + Bill # 21

Do you have any pre-planned logic...

PS: Dont forget to enable the macro. ;)
Click to expand...
Hi Deb,

I have a query on the excel. it is sum of two values. but there may be chances that even 3 value sum is required:(..
 

Hi Navi,

you just have to test all sums from two cells to all range, a very basic combinatory sum …

Regards !
​
 
Hi Deb,

Thanks a ton:):)solver works very wel :)

Last and finaly query, if my value is 100, solver gives only first combination of cells which counts to 100, there are more values were combiationscomes to same. is there a way for the final one.

Many thanks for everyone here who helped me in providing solutions

Regards,
Navi
 

Navi,

from your sample workbook with only 26 amounts, the scan for value 11555.6 takes 234 seconds
on a i5 2.5GHz laptop with my own old combinatory sum code.
As the process time increases exponentially with the amounts number,
so with 1000 amounts, could you imagine it will take ages ‼

In a financial or accounting application, it's very dumb to search only upon amounts !
Better is to start to filter with customer ID for example or any criteria that reduces the amounts number …
​
 
Hi Marc,

Thanks for your reply, but by filtering customer Id, currency and location itself we find around 50 to 75 bills.. So manually finding the combinations takes more time
 

Hi Navi,

have you ever take a look to the code from Deb's link ?
'cause it is incredibly fast !

Regards !
​
 

I like your video but frankly my favorite is the link's code which responds insane instantly ! :eek:
Needs a mod or two maybe to be perfect but it was since a long time a code extracts me a WhoW ! :DD​
 
@Debraj(ex-Roy)
Hi, buddy!
My congratulations for the video, well done and keep on doing so. :cool:
Regards!
 
You must log in or register to reply here.
Back
Top