Combine two data sheets based on date and sum

%3CLINGO-SUB%20id%3D%22lingo-sub-1959686%22%20slang%3D%22en-US%22%3ECombine%20two%20data%20sheets%20based%20on%20date%20and%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1959686%22%20slang%3D%22en-US%22%3EHi!%20I%20have%20two%20data%20sheets.%20One%20is%20with%20cheque%20details%20(%20Cheque%20date%20and%20cheque%20amount).%20The%20other%20one%20includes%20due%20payments%20(%20due%20invoice%20amount%20and%20due%20date).%20How%20can%20I%20generate%20combinations%20of%20different%20cheques%20which%20can%20be%20given%20to%20different%20due%20invoices.%3CBR%20%2F%3EExample-%3CBR%20%2F%3Esupplier%20A-%20%24500%20due%20on%2010%2F12%2F2020%3CBR%20%2F%3ESupplier%20B-%20%24%20200%20due%20on%2010%2F12%2F2020%3CBR%20%2F%3ESupplier%20C-%20%24100%20due%20on%2015%2F12%2F2020%3CBR%20%2F%3E%3CBR%20%2F%3EPost%20dated%20cheques%20received%20from%20customers%3CBR%20%2F%3E%2470%20dated%2010%2F12%2F2020%3CBR%20%2F%3E%24150%20dated%2010%2F12%2F2020%3CBR%20%2F%3E%24%20220%20dated%2012%2F12%2F2020%3CBR%20%2F%3E%24160%20dated%2010%2F12%2F2020%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20can%20I%20get%20different%20Post-dated%20cheques%20suggestions%20to%20match%20with%20due%20payments%20so%20that%20the%20shortage%20can%20be%20identified%20for%20each%20due%20payment%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1959686%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1960260%22%20slang%3D%22en-US%22%3ERe%3A%20Combine%20two%20data%20sheets%20based%20on%20date%20and%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1960260%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F892278%22%20target%3D%22_blank%22%3E%40MADHUSHIKASH%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20hard%20to%20give%20help%20without%20seeing%20the%20actual%20sheets%20you're%20working%20with.%20If%20you're%20able%2C%20without%20revealing%20confidential%20information%2C%20to%20post%20the%20actual%20spreadsheets%2C%20we%20could%20give%20much%20more%20specific%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20absence%20of%20that%3A%3C%2FP%3E%3CUL%3E%3CLI%3Eyou%20need%20to%20have%20some%20way%20to%20identify%20each%20customer%2Fvendor%2Fpayee%20and%20with%20a%20%3CSTRONG%3Ecommon%20identifier%3C%2FSTRONG%3E%20across%20both%20sheets.%3C%2FLI%3E%3CLI%3Ethen%20possibly%20use%20functions%20like%20MATCH%20or%20VLOOKUP%20or%20XMATCH%20or%20XLOOKUP%20to%20make%20the%20connections%3C%2FLI%3E%3C%2FUL%3E%3CP%3EPlease%20post%20a%20copy%20(or%20a%20mockup)%20of%20your%20data%20if%20you%20need%20more%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor
Hi! I have two data sheets. One is with cheque details ( Cheque date and cheque amount). The other one includes due payments ( due invoice amount and due date). How can I generate combinations of different cheques which can be given to different due invoices.
Example-
supplier A- $500 due on 10/12/2020
Supplier B- $ 200 due on 10/12/2020
Supplier C- $100 due on 15/12/2020

Post dated cheques received from customers
$70 dated 10/12/2020
$150 dated 10/12/2020
$ 220 dated 12/12/2020
$160 dated 10/12/2020

How can I get different Post-dated cheques suggestions to match with due payments so that the shortage can be identified for each due payment?
1 Reply

@MADHUSHIKASH 

 

It's hard to give help without seeing the actual sheets you're working with. If you're able, without revealing confidential information, to post the actual spreadsheets, we could give much more specific help.

 

In the absence of that:

  • you need to have some way to identify each customer/vendor/payee and with a common identifier across both sheets.
  • then possibly use functions like MATCH or VLOOKUP or XMATCH or XLOOKUP to make the connections

Please post a copy (or a mockup) of your data if you need more help.