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

Need help : Macro for combination of number that sum/match to a value

N

Nang

New Member
Hi


I need your big help here.


I have a set of data in a column which have vary numbers, both positive or negative values. What I want to do is to find the match combination of that data which sum to a target value.


For example in column A has value which range is from -3 to 500, and I want to find only one possible combination that will sum to 300.


I had find and use the code from below web:

http://www.tushar-mehta.com/excel/templates/match_values/index.html


but I find some problem when it has a negative value, because the code's creator said that negative value should be at the first line.

My data was sorted based on the date, so the negative value can be in the middle of the data.

When I ran the code, the excel will not responding for very long time and it ends with error.


How should I build the macro ?


Many thanks for your help.
 
Hi Nang ,


Do you have any idea what is the number of items , which when added together will give you the target value ?


I'll tell you what I tried. I took the following items of data :

[pre]
Code: 
2
0
-123
30
110
-33
123
130
-77
90
60
127
and ran it through Tushar Mehta's code ; the macro gave me the warning , but since the number of elements is small , it completed execution , and gave me the correct result using 5 items from the list.


Next , I saw the minimum number in the list is -123 ; I created another list as follows :

2
615
0
153
233
90
246
253
46
213
183
250
[/pre]
where +123 ( ABS(-123) ) has been added to each of the elements of the earlier list , and the target has been revised to 615 ( 123 * 5 ) ; when I ran the macro on this new data list , I got the same answer as before.


Can you see if this helps in your case ?


Narayan
 
I'm sorry but I still confuse here.


Would you please to try with my data example below, and I only want to find the first combination.


Example:

-Target value : 120,662,010


-Data:

(106,623)

117,720

3,125,340

3,757,320

2,984,220

7,549,020

805,140

5,177,700

(649,800)

10,437,660

1,741,410

10,930,410

3,883,320

10,905,840

4,009,050

7,014,600

4,419,000

11,060,730

4,314,510

6,577,020

7,472,790

1,785,330

2,979,720

512,820

2,317,590

21,937,140

2,472,750

12,261,330

1,321,020

4,093,200

3,871,620

1,931,220

4,954,230

3,000,150

1,837,170

2,790,090

2,828,520

1,496,880

3,380,940

8,201,340

5,192,280

3,260,880

1,358,820

3,869,460

1,078,200

1,704,060

1,887,300

424,170

703,620

570,150

1,035,900

2,106,810

4,359,330

2,291,400

1,014,840

1,268,820

10,118,160

4,230,540

7,720,110

7,054,290

1,747,800

617,490
 
Hi Nang ,


The problem , as far as I can see , is not because of the negative numbers ; there are only two of them.


The problem is that your target value is so much higher than the elements in your list , that a large number of elements , say 30 to 40 , may be required to get your target. This is what is taking so much time.


I just removed the two negative numbers , and tried with the same target , and all the positive numbers ; the result was immediate.


Narayan
 
Nang

You may also want to try sorting your list of numbers from Highest to Lowest

That should speed it up
 
Thank you All for your advice.

I have tried all you solution before, and yes they worked.

But unfortunately, I can't use them. Because as I mention above, the data should be ordered by the date which mean the negative value can be in the middle of the line.

And I'm just want to find the first possible combination only (not all possible combination).


Actually I want to do bank reconciliation here. The data from the bank just show the total payment from a customer without invoice reference, and I should match it against my invoice data. My problem is the payment from customer is not sequential. For example if there are invoice no 1, 2, 3, 4, and 5. The customer only paid for invoice no 1 and 3. Thats why I can't do your advice Hui.


I have changed the code below :

MaxSoln = Selection.Cells(1).Value


into :

MaxSoln = 1


But it still cause the excel not responding for a long time and it ends with error again.


Do you have another suggestion on another code that I can use ?


I'm totally desperate here.
 
Hi Nang ,


You have not explained why you cannot remove the two negative numbers , and work with only positive numbers.


You can try with smaller target values , and a smaller range of numbers , so that you reconcile in batches instead of one big batch.


I am sorry , but I cannot help further in this matter.


Narayan
 
Hi Narayank


Thanks for your attention.

Yes it is a difficult condition for me to solve.

The target values is always that big, and some times in billion values.

It was the payment that the customer did.

And for the negative value, it can't be remove because it is the overpaid value that I don't know to which payment it was for. If I knew it, I will add it to the correct payment first before I run the code.


Hope another maybe can help here.

Thanks anyway.
 
Hi Nang ,


I think your best bet is to go through these two links :


1. http://www.mrexcel.com/pc09.shtml


2. http://www.mrexcel.com/forum/excel-questions/106063-accounts-receivable-challenge-august-2002-a.html#post1044770


The author of the winning solution , IOANNIS , says that his winning macro will not work without the entire workbook ; he will send you the workbook if you email him.


Narayan
 
Hi Nang ,


Tushar Mehta's macro is not related to the workbook ; can you not copy your columns containing the invoice numbers and the invoice amounts to a new workbook , copy the macro to this new workbook , and run it after sorting the two columns on the amounts ?


Once you know which invoices are to be combined to give the target value , you can go back to your parent workbook and do whatever is required.


Narayan
 
Thanks Narayank for the information :)


I'll contact IOANNIS asap.

I'm sorry if I can't apply your last solution neither due to the user needs.

They don't want to search it manually again for all possible combination.

If you try to delete all the negative values, the possible combination of it is more than 200 combination.

It seems like my problem with the code is on the value (106,623) that makes the code error. If I delete it, the code works. Still confuse with it.
 
Hi Narayank,


It seems like IOANNIS never online since 2007.

Do you have his email addres or maybe his workbook?
 
Hi Nang ,


Sorry , no I don't have the workbook.


Is it possible you can upload your official workbook , on which you want to apply the macro ?


You can make a copy , and in the copy delete all unnecessary / confidential information , and then upload that copy.


Narayan
 
Hi Nang,


This kind of number matching problems can take a very long time to resolve in a macro.

There is a function in Excel that gives you the number of combinations of your transactions that should be checked looking for your sum.

For example if you need to check if there are combinations of your 62 transactions that include exactly 31 numbers from your list that add up to your target value you can calculate it like this:

=COMBIN(62,31) - it's a very LARGE number.


so... you can use a VBA macro or you can use a shareware add-in like this one:


http://www.evolucion.com.au/Admin_Pages/SumMatch.aspx


cheers,

catpol
 
Hi.... I need help on the similar issue, as I have a set of data in a column which have vary numbers, but only positive numbers. What I want to do is to find the match combination of that data which sum to a target value and if not exact sum then closest sum to a target value.


Thanks in advance,

Sameer
 
Sameer


Firstly, Welcome to the Chandoo.org Forums


Did you go through the 3 links listed in the comments above?


Start there and then let us know how you went
 
You must log in or register to reply here.
Back
Top