I'm looking for assistance with an index/match type situation where I have a transaction table containing all the unique transaction codes which I then need to append to an invoice table.
To assist with the matching, I created a unique ID in each table consisting of Account#+TransDate+TransAmt.
This works fine unless a customer makes multiple payments on the same day in the same amount. Unfortunately, this happens a lot. As you can see in the image, my formula fails in this situation because it is always returning the first match. I would like to find a way to extract and append the subsequent matches.
Here is an example of the raw invoice data as it is received (csv) before I import it into the invoice table.
With regard to the TransID's in the trans table, the number to the left of the decimal is the 'batch' number for that day's payments. The number to the right of the decimal indicates the payment's position in the batch file. If there is only one payment for the day, there will be no number to the right of the decimal. In this particular example, the four trans id's highlighted in yellow are sequential but this is not always the case. Note how Acct# 4628 has two payments on 20210226 but the trans ids are 52144.1 and 52144.12.
Any suggestions on how to handle this much appreciated! Totally open to other methods for solving it.
To assist with the matching, I created a unique ID in each table consisting of Account#+TransDate+TransAmt.
This works fine unless a customer makes multiple payments on the same day in the same amount. Unfortunately, this happens a lot. As you can see in the image, my formula fails in this situation because it is always returning the first match. I would like to find a way to extract and append the subsequent matches.
Here is an example of the raw invoice data as it is received (csv) before I import it into the invoice table.
With regard to the TransID's in the trans table, the number to the left of the decimal is the 'batch' number for that day's payments. The number to the right of the decimal indicates the payment's position in the batch file. If there is only one payment for the day, there will be no number to the right of the decimal. In this particular example, the four trans id's highlighted in yellow are sequential but this is not always the case. Note how Acct# 4628 has two payments on 20210226 but the trans ids are 52144.1 and 52144.12.
Any suggestions on how to handle this much appreciated! Totally open to other methods for solving it.
