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!!!
