Macro for Combinations needed

[Navi]

Hi all,

Need a help here.

I need a macro code, for finding permutation and combinations.

Inputs: Column 1 has invoice numbers say 1 to 100
Column 2 will contain amounts for the invoices.

If i update an amount in any cell in column 3, excel should keep checking for amonts in column two by adding combinations and provide the result.

eg, column 2, cell 1+2,1+3,1+4 then 2+3 2+4 then 1+2+3 and so on and has to check the entire combinations and provide me the result.

Result can be exact match or 1 dollar round off.

kindly let me know if this can be done.

let me know if any further details required.

Thanks
Naveen

 

[shrivallabha]

How many real rows of data you have? And normally, what is the number of invoices that you look for combinations? It will be huge calculation effort (even if it is in memory).

For smaller numbers the combinations are less. e.g.
100 with 1 invoice will have 100 combinations.
but
100 with 10 invoices will have 17310309456440 combinations.
So if your real data is few thousand invoices then it will be huge task.

 

[shrivallabha]

And in the past there was a challenge on MrExcel which worked on similar lines.
http://www.mrexcel.com/pc09.shtml
It may prove useful.

 

[Navi]

data may vary. each invoice amount may vary from $ 100 to $ 200,000.

if we get payements for instance $ 150,000. we need to find out from the input invoices how many invoices amount can match to $ 150,000. Max 150 line items will be in input field.
I have taken an code from the link "http://www.tushar-mehta.com/excel/templates/match_values/#VBA_multiple_combinations" but it takes more than 3 hours to solve hence needed any time consuming one.

 

[Marc L]

Hi !

Navi, the method of your link is the fastest I know under VBA !
Mine is pretty on the same principle, so same execution time …
The more lines, the more time execution !
As VBA is one of the slowest language …

I don't know the way of shrivallabha's link, did you ever try it ?
​

 

[Navi]

Hi Marc, I got the code from Shrivallabha's link but unable to read the post and references as page is not opening.

 

[NARAYANK991]

Hi ,

I doubt that any code will execute in the seconds or even minutes that you are looking for , since the issue is not the code but , as Shrivallabha pointed out , the number of combinations.

If you can put a limit on the number of combinations which need to be tested , then code can be written to execute in a reasonable amount of time.

Narayan

 

[Navi]

Hi Narayan,

The maximum rows will not exceed more than 150.

 

[NARAYANK991]

Hi ,

The critical point is the number of additions that need to be tested ; even if there are a thousand rows , if the number of additions is only 2 , it will be manageable , but as the number of additions increases to 3 , 4 and more , the number of combinations increases dramatically.

Given 150 cells of data , if the number of additions to be tested is 5 , then the test cases become nearly 600 million.

Narayan

 

[Navi]

Hi Narayan,

client may make a bulk payments and hence we will not be in place to see if its two combinations or three. we will just need to look out for more possible ways.

 

[NARAYANK991]

Hi ,

In which case you have to use the code to try and match as many payments as possible by restricting the number of additions to 3 , and take care of the remaining by manual checking. You can save some time at least.

Narayan