Excel Macro V-lookup to Alteryx

[VEDH]

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:

1. 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)

2. 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).
3. 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.

[wwatson]

The interactive videos in this section of the Alteryx Academy should help

https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Excel%20Us...

[Qiu · Accepted solution]

@VEDH 
I think I may miss something for the data processing part.

Anyway, for input, we use one input tool with wildcard to bring in all 6 files.

For output, there is an option to output multiple sheet Excels.

Kindly check the Join part, I am not sure I get your correctly.

[VEDH]

Hi @Qiu

Thanks a lot for the help.

Yes, Join tool is what I initially thought of and is indeed getting the job done.

I am more concerned with the output part as  I still haven’t figured out how to split those custody data back to 6 sheets of an excel.

[Luke_C · Accepted solution]

Hi @VEDH 

I think @Qiu 's solution has the correct approach for the output you want, I see separate tabs for each. This is done in the output tab and the below configuration. As part of reading in the inputs the file names are also kept. This allows Alteryx to output one tab for each file name. 

[Qiu]

@Luke_C 

Thank you very much for the detailed elaboration. 😁

[Qiu]

@VEDH 
Kindly download the workflow and give a test run to see if this is what you need, in case you have not. 😁

[VEDH]

@Qiu

Yes, my bad. It's working.

Thanks a lot for the help. 

[Qiu]

@VEDH 

Glad to help and thanks for the accept mark. 😁