Joining Approximate Matches

[kevin-lindorfer]

Hello!

I am working to match items from a bank statement to sales for a bank reconciliation. As with most places handling cash, the amounts of the deposits do not always match the amount of sales due to cash over/short, and the lag time at the bank may cause there to be a 2-4 day difference in date.

I am able to find many matches by Joining with Location ID, Amount, and Date. All of these must be exact though for them to match.

I am trying to find a way to make the following data join together, within say $5 and within 5 days to create approximate match. Fuzzy Match could be a helpful tool here, but I am not sure.  

Data 1:

Date	Amount	Location ID	Description
7/27/2017	75.00	LAS	Sales
8/01/2017	1500	ATL	Sales
8/03/2017	457.91	DAL	Sales
8/04/2017	150	STL	Sales

Data 2:

Date	Amount	Location ID	Description
8/01/2017	75.00	LAS	Cash Deposit
8/04/2017	1498.97	ATL	Cash Deposit
8/08/2017	457.93	DAL	Cash Deposit
8/27/2017	150	STL	Cash Deposit

In the ideal world, the output would look like this:

Date	Amount	Location ID	Description	Date	Amount	Location ID	Description
7/27/2017	75.00	LAS	Sales	8/01/2017	75.00	LAS	Cash Deposit
8/01/2017	1500.00	ATL	Sales	8/04/2017	1498.97	ATL	Cash Deposit
8/03/2017	457.91	DAL	Sales	8/08/2017	457.93	DAL	Cash Deposit

I would definitely need the Location ID to match perfectly, but is there a way to build this logic into a join tool using formulas or some other tool to get those amounts to show as matches? I would not want the last record with STL to match because it is outside of the date range set. 

I also attached a flow with sample data if this helps.

Thank you!!!

[BenMoss]

Here's my attempt. Loved this challenge btw.

I rounded values to the closest 50 and then did a +4 days on the sale date.

If not perfect it should definitely help you get to where you wish to be.

Ben

[BenMoss · Accepted solution]

I've generated a workflow that instead of rounding values to 50, works on the +-5 logic you outlined in your post. I missed this.

Ben

[kevin-lindorfer]

This works perfect for what I need it for! Thank you SO much!

[sanchezj]

thanks for this solution! it looks the logic is just +5 (Could you add the -5 logic)?

if a date is 27th August, it should be counted from the margin of +/-5 i.e., 22nd August and 1st October? 

Is there other method that does not create new rows? it would be heavy computational cost if you consider big datasets. I want to join two data sources on the time-stamp with a margin of 3 minutes i.e., +/-3. 

Thanks.