DESPERATE FOR HELP: FORMULA TOOL HELP

[Vijay_Vittal_11]

I need to write a formula to solve the below scenario.

I am an auditor performing intercompany transactions testing: 

I have two files:

1. Receipts

2. Payments

The Manual way I do:

In the Payments file I have three columns and in the receiver's file I have three columns. 

1. Account Number

2. Amount

3. Bank's weird comment containing the receiver's account number. My goal is to match $ 1,000,000 sent from account A in the payments file to account B in the receiver's file using this comment column.

Bank's comment usually look like this : #p9e29u rdshis fsuf sufw 9279572947 sgfu wqfs fs fshfgis fgsoif saf #hhaifgusa fguosa ou h 25472905479 ajfisfsai fyh 0264290872) bold underline is the receiver's account number I made for your reference banks does not bold/underline it). The problem is sometimes the comment can have other related account number which is not a receiver's account number.

[Tam]

@Vijay_Vittal_11 can you provide more details please:

What is the customer account number length and is it a fixed length (i.e. 10 characters). 

Are there unique feature that identify an account number from just some random series of string (i.e first 5 digits of a 10 digits number are always 43678 in)

Are the other numbers sometime have the same length as the customer account numbers. 

[BRRLL99 · Accepted solution]

To extract the receiver's account number from the bank's comment column in both the Receipts and Payments files, you can use Alteryx's Regex tool.

Here's an example workflow:

1. Use an Input Data tool to read in the Payments file and select the three columns: Account Number, Amount, and Bank's Comment.

2. Use a Regex tool to extract the receiver's account number from the Bank's Comment column. Use the following Regular Expression:

   (?<=\s)\d{10}(?=\s)

   This regex looks for 10-digit numbers surrounded by spaces.

3. Use a Select tool to rename the new column created by the Regex tool as "Receiver's Account Number".

4. Use a Filter tool to remove any rows where the Receiver's Account Number is null or empty.

5. Use a Summarize tool to group the data by the Receiver's Account Number, and calculate the sum of the Amount column.

6. Use an Output tool to write the data to a new file.

7. Repeat steps 1-6 for the Receipts file.

8. Use a Join tool to combine the two datasets based on the Receiver's Account Number.

9. Use a Filter tool to remove any rows where the sum of the Payments and Receipts do not match.

10. Use an Output tool to write the final data to a new file.

This workflow should extract the receiver's account number from the bank's comment column and match the payments to the receipts based on the account number.

[Raj]

Can you share some sample rows of data?