Formula for matching

[Carlyn]

Hi

I have data coming from 2 different sources that I am trying to create a formula for to match line items.

I have attached a sample of the data. 

Basically, I need the formula to look for any RC documents coming from the SIS source and then check if there are any Bank rows that have the same Transaction Date, Amount, and Alt_Account and if they match across all 3 columns then return "MATCH" in a now column on both rows that are matching.

Is this possible?

[OTrieger · Accepted solution]

@Carlyn 

There are many ways how to do that
1. filter tool splitting between Bank and SIS and then join them back on all 3 criteria, in the J you will get al the matching items
2. Use Summarized tool Grouping on these 3 criteria and Concatenating based on Source and then count Source, any line that will have Count 2 will indicate a matching lines
3. Use Unique tool setting it for the 3 criteria then you will have on the D output all the lines that are duplicates, then connect it with Join tool to the rest of the data based on your 3 criteria and you will have all the matching items comes in J
4. Sort the data based on all 3 criteria then added a 3 Multi Row Formula tool and check if in each one if the data in the above row is equals to the row bellow it, now you will have 3 flags, then you can check where you have 3 flags that are true will indicate those lines that are matching
I'm sure that others can share more ways on how to do it.

[Carlyn]

Thank you, I knew there would be multiple ways, I was just struggling to work them out.

The first option worked, but would you be able to recommend a way to make it match, if the date is not exact but is within 15 days, so if the bank date is the 1st, and the SIS date is the 16th, but the other columns match?

[OTrieger]

In this case drop the Date as a matching criteria and after the matching you can use a formula:
DateTimeDiff(Date1,Date2,"Days") and then you can create a condition if bigger or smaller than specific number of days.