Hi,
I am quite new to Alteryx, and need help on the project currently working on where we are replacing an existing excel macro with Alteryx. The macro is currently being used to filter out matched payments.
We have our ledger file in excel which represents all the matched and unmatched payments. Ledger would just reflect matched payments as matched rest are shown as blank. Daily 6 different custodies send the statement in excel file for confirming the payments. Custody's statement are compared with our ledger to figure out the unmatched.
How does the macro works:
- From our ledger data, 4 columns (Fund, SEDOL, Payment Date and Net Amount) are combined together in a single cell for every row on Sheet 1. Refer below snap from ledger sheet.
Formula used to combine them was =B2&C2&D2&E2
Once the value was received, the formula was removed to get a string value. (Note that the date got converted to 44278)
- The custody statement will also consist of the same data – Fund, SEDOL, Payment Date and Net Amount. Hence, macro would combine the same for custody data as well on column 1 in remaining 6 sheets respectively (as there are 6 custodies).
- Then, with the help of V-lookup on each custody sheet the status would reflect as matched if the status was matched on the ledger sheet.
I have already created a half workflow on this where I’ve imported all the data to Alteryx from different sources, cleaned them (as the files were in different formats of excel with different headers and column position). Now I wanted to know is there any better approach to get the job done or shall I proceed with the same approach as was used in existing macro. If the same approach as the macro is to be used, then how to join or find and replace 1 file (ledger file) with multiple files (custody files).
The output must be in a single excel file. But should be in 6 different sheets as per custodies.
Due to data confidentiality I could not send the exact data. Hence, attached dummy sample data which I created myself.
