Want excel to give a combination of a few different numbers with give same figure as sum

[jayarajmarydasan]

CUSTOMER NAME JOB NO DOCUMENT NO DOCUMENT DATE CURRENCY CODE LOCAL AMOUNT DR CR DUE DAYS
AAA COMPANY TE-150621620080-1 OLT-INV-50621620297 27-Jun-2015 AED 35.00 D 4
AAA COMPANY TE-150521620149-1 OLT-INV-50621620132 13-Jun-2015 AED 17.00 D 18
AAA COMPANY TE-150421620208-1 OLT-INV-50521620268 31-May-2015 AED 20.00 D 31
AAA COMPANY TE-150521620008-1 OLT-INV-50521620263 31-May-2015 AED 14.00 D 31
AAA COMPANY TE-150521620018-1 OLT-INV-50521620194 23-May-2015 AED 24.00 D 39
AAA COMPANY TE-150421620272-1 OLT-INV-50521620193 23-May-2015 AED 29.00 D 39
AAA COMPANY TE-150521620027-1 OLT-INV-50521620173 23-May-2015 AED 27.00 D 39
AAA COMPANY TE-150421620239-1 OLT-INV-50521620070 10-May-2015 AED 19.00 D 52
AAA COMPANY TE-150421620219-1 OLT-INV-50521620067 10-May-2015 AED 44.00 D 52
AAA COMPANY TE-150421620167-1 OLT-INV-50421620361 28-Apr-2015 AED 16.00 D 64
AAA COMPANY TE-150421620133-1 OLT-INV-50421620337 27-Apr-2015 AED 15.00 D 65
AAA COMPANY TE-150421620118-1 OLT-INV-50421620255 22-Apr-2015 AED 1.00 D 70

Refering to the above data as an example, assume that this is the invoice details of my company to another company AAA comapany.

I got cheque for amount Aed 36.00 from customer(assuming the customer didnt give invoice details), whether excel can give the possible combinations that the sum of amounts on column "Local amount will give this amount" that will match 36?
Say if 36 is the amount then it can be (a) either 35+1 or (2)19+17 or (3) 20+16
It am looking working like Excel can hint it either a combination or some highlighting etc


This is a small range, but if it is big, it becomes complicated and time consuming. Is there any formula or function we can use to easily find this? See Attachment cheque amount against invoice amounts.xlsx

[Kaper]

My proposition - write your 36 in say J1, then in J2 and copy down formula:

Please Login or Register  to view this content.

note that for half of amount (18) in one of rows it can find the same row twice

[Crooza]

Zeszyt more than 2 up to 4.xlsm

It occurred to me that you may have more than 2 invoices that when added together match the target value. run the attached macro (see file attached) to get combinations of up to 4 invoices that match the target. You can experiment by adding extras but just add some text to column b so it knows which ones to test for.