Home

Finding number combinations that equals to a specific amount.

%3CLINGO-SUB%20id%3D%22lingo-sub-484399%22%20slang%3D%22en-US%22%3EFinding%20number%20combinations%20that%20equals%20to%20a%20specific%20amount.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-484399%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Excel%20Experts%2C%3C%2FP%3E%3CP%3EIts%20been%20days%20and%20I%20still%20can't%20find%20the%20answer%20to%20my%20problem.%20I%20tried%20many%20different%20approach%20but%20nothing%20is%20working%20for%20me.%3C%2FP%3E%3CP%3EI%20have%202%20columns%20with%20amounts.%20Let%20say%20these%20are%20invoices%20amount%2C%20and%20we%20have%20a%202%20reports%20using%20to%20compare%20if%20they're%20equal.%3CBR%20%2F%3EThese%20columns%20is%20suppose%20to%20be%20equals%20but%20unfortunately%20there%20is%20a%20difference.%20The%20discrepancy%20is%20597.27.%3CBR%20%2F%3EBoth%20columns%20has%204k%2B%20counts.%20I%20tried%20using%20the%20add-in%20Solver%20of%20Excel%20but%20it%20can't%20process%20it%20because%20of%20high%20number%20of%20counts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20find%20those%20invoices%20that%20has%20a%20total%20of%20597.27%20which%20is%20the%20discrepancy%20in%20one%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20much%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20upload%20the%20file%20if%20anyone%20needs%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-484399%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-484829%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20number%20combinations%20that%20equals%20to%20a%20specific%20amount.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-484829%22%20slang%3D%22en-US%22%3EIf%20the%20Invoice%20Amounts%20are%20in%20Columns%20A%20and%20B%2C%20and%20Row%201%20are%20your%20Column%20Labels%2C%20you%20can%20use%20this%20formula%20in%20C2%2C%20copied%20down%20rows%3A%3CBR%20%2F%3E%3DA2-B2%3CBR%20%2F%3EAny%20row%20under%20Column%20C%20that%20returns%20an%20amount%20other%20than%200%20indicates%20inequality.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-484906%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20number%20combinations%20that%20equals%20to%20a%20specific%20amount.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-484906%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20reply.%20I%20already%20done%20that.%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20look%20for%20a%20numbers%20combination%20totaling%20to%20a%20specific%20amount.%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20of%20the%20guides%20I've%20found%20is%20to%20solver%20add-in%20in%20excel%20but%20this%20extension%20can't%20proceed%20my%20columns%20because%20I%20have%204k%2B%20records.%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I'm%20looking%20for%20alternative%20way%20with%20the%20same%20functionality.%3C%2FP%3E%3C%2FLINGO-BODY%3E
jigger87
New Contributor

Hi Excel Experts,

Its been days and I still can't find the answer to my problem. I tried many different approach but nothing is working for me.

I have 2 columns with amounts. Let say these are invoices amount, and we have a 2 reports using to compare if they're equal.
These columns is suppose to be equals but unfortunately there is a difference. The discrepancy is 597.27.
Both columns has 4k+ counts. I tried using the add-in Solver of Excel but it can't process it because of high number of counts.

 

I'd like to find those invoices that has a total of 597.27 which is the discrepancy in one column. 

 

Any help would be much appreciated.

 

I can upload the file if anyone needs it.

 

2 Replies
If the Invoice Amounts are in Columns A and B, and Row 1 are your Column Labels, you can use this formula in C2, copied down rows:
=A2-B2
Any row under Column C that returns an amount other than 0 indicates inequality.

Hi @Twifoo,

 

Thanks for the reply. I already done that. 

I'm trying to look for a numbers combination totaling to a specific amount. 

One of the guides I've found is to solver add-in in excel but this extension can't proceed my columns because I have 4k+ records. 

So I'm looking for alternative way with the same functionality.

Related Conversations