Joining data when there are same dates and amounts

[Carlyn]

Hi All

I am trying to match large volumes of data where there are sometimes multiple rows on both inputs which have the same transaction date and the same value.

I have tried using Multi Row Formula to pull in a column with the count of the unique values. I have also added in Tile to see if this helps. However, I still end up with duplicate rows on my output. I then tried to use Unique to eliminate the additional rows that are being created, but the issue seems to be when there are also duplicate amounts on the 2nd input, the output is pulling in the same row/item from the 2nd input to match to the 1st input.

Sorry it is hard to explain. 

I have attached an excel with the inputs and the outputs that I am seeing, along with a snip of the workflow that I have been working on as hopefully this helps to demonstrate the issue.

Can anyone help with where I am going wrong?

Thanks

[OTrieger]

@Carlyn have you tried adding unique tool on each input file before merging the data?

[Carlyn]

Hi - this won't work as that would strip out the amounts that do correctly have the same transaction date and amount. Unless I am missing something.

[OTrieger]

Then how do you know that there are duplicates? Or what will be the criteria to indicate that you have duplicates?

[TUSHAR050392]

Hey @Carlyn, I am unclear about the ask here because in your attached example, the transaction dates are different with same amount but you have highlighted it as same so I also want to understand what is your criteria. If the transaction date and amount is same then you can probably just use Unique tool by selecting transaction date/ amount and get rid of the duplicates before joining the data.

[Carlyn]

HI, thank you for responding, the issue is that the bank input has 2 transactions for the same amount and the match to the SIS input is for the same amount in the same Alt account within 90 days. 

So what I should end up with is the bank entry on the 9th of April matching with the SIS entry on the 16th of April and the bank entry on the 13th of April matching with the SIS entry on the 14th May.

When I first join them it duplicates the entries so I end up with 4 bank entries, so I tried to use the unique tool, but the output is giving me both bank transactions for the 9th and 16th of April, picking up the first SIS transaction so they are both matching with the 16th of April.

Does that make more sense?

[Carlyn]

Hi, thank you for responding. The duplicates are coming from my first join. because there is a formula that says, if the amount and the Alt_Account are the same, and the transaction date on the SIS input is within 90 days of the bank date then they should match. This means that the bank entries pick up both SIS entries twice.

I then tried the Unique tool which fixes the bank side, but the SIS side is showing that both bank entries are picking up the same SIS entry.

[OTrieger]

@Carlyn thanks.

What you can do however will not work when you will have 3 or more transactions:
Add 2 Summarize Tools after the Join Tool, one is set to Max date and then other to Min date and in that manner you will be able to match the 2 first transactions and then 2 recent transactions. However that will not work if there will be a 3rd and 4th transactions that will need to be matched as it will take only the first Max and the first Last.

[Carlyn]

Thank you.

Yes, there will be times that I have more than 2 transactions on the bank with the same amount.