Matching Items Across Input Files with Duplicate Items

Question

Hello,

I was looking around the community here for a solution to see if someone encountered something similar. I see it in bits and parts and I was hoping someone could help on the below ask:

The goal is to try to match items across 2 reports using the description, currency and amount. I think we can use the join tool but am struggling with how to link them together since we want to match the amounts based on Description, Currency & Amount. Also if there are extra items in either report after the netting off process, with the same description/currency/Amount how can I set up the workflow to indicate these items as extra items via output?

I've indicated below an example and expected result of what I'm trying to achieve.

Really appreciate any guidance

Example Data      Report One Report TwoDescription CurrencyAmount Description CurrencyAmountMeal ExpenseCAD5.57 Meal ExpenseCAD5.57Transportation ExpenseUSD10 Transportation ExpenseUSD10Transportation ExpenseCAD10 Transportation ExpenseCAD10Transportation ExpenseUSD10 Travel ExpenseCAD15.34Miscellaneous CostCAD9.22

Expected Result - 3 Outputs             1. Matched Items             Report One Report TwoDescription CurrencyAmount Description CurrencyAmountMeal ExpenseCAD5.57 Meal ExpenseCAD5.57Transportation ExpenseUSD10 Transportation ExpenseUSD10Transportation ExpenseCAD10 Transportation ExpenseCAD10       2. Unmatched Items Report One      Report One    Description CurrencyAmount    Transportation ExpenseUSD10    Miscellaneous CostCAD9.22           3. Unmatched Items Report Two      Report Two    Description CurrencyAmount    Travel ExpenseCAD15.34

AncientPandaman · Answer

agreed, add 1 more criteria to differentiate the duplicate item. tile tool is good to use here.

lwolfie · Answer

Did you figure this out?  I would suggest a record ID based on the combination of the 3 columns.  Then when you match on RecID, and the first 2 columns, the extra row will fall out.

hannah1818 · Answer

Thank you! That really helps with setting the multiple criteria. The only thing is that for instance with the "Transportation expense"  I want the extra item from report 1 that is not matched with report 2 to be indicated as not matching as well. Currently its showing as matched since it has the same Description/ Currency/ Amount with another items in the same report. Is there a way to indicate that the amount is still unreconciled?