Matching Items Across Input Files with Duplicate Items

[hannah1818]

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 Two
Description	Currency	Amount		Description	Currency	Amount
Meal Expense	CAD	5.57		Meal Expense	CAD	5.57
Transportation Expense	USD	10		Transportation Expense	USD	10
Transportation Expense	CAD	10		Transportation Expense	CAD	10
Transportation Expense	USD	10		Travel Expense	CAD	15.34
Miscellaneous Cost	CAD	9.22

Expected Result - 3 Outputs
1. Matched Items
Report One		Report Two
Description	Currency	Amount		Description	Currency	Amount
Meal Expense	CAD	5.57		Meal Expense	CAD	5.57
Transportation Expense	USD	10		Transportation Expense	USD	10
Transportation Expense	CAD	10		Transportation Expense	CAD	10
2. Unmatched Items Report One
Report One
Description	Currency	Amount
Transportation Expense	USD	10
Miscellaneous Cost	CAD	9.22
3. Unmatched Items Report Two
Report Two
Description	Currency	Amount
Travel Expense	CAD	15.34

[OTrieger]

@hannah1818 
Hello Hannah,
You wrote what is the answer using Join Tool. You can set several criteria for the join. You an add as many as you want, in your case you will need 3. L Currency R Currency, L Amount R Amount and L Description R Description 
L you have the unmatched from L side
J you will get the Matched items
R you will have the unmatched from R Side

[OTrieger]

So now you will have 3 outputs, 1 from L, one from R and one from J.

[hannah1818]

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?

[lwolfie]

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.

[PangHC]

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