![search multiple excel files at once search multiple excel files at once](https://support.content.office.net/en-us/media/3286ef6d-051e-4404-b216-57835c922dcb.png)
The rows coming out of the L anchor will the the Ledger rows that did NOT find a match in the Custody data set so we'll update the Match Status to null/blank. Now we'll perform a single Join tool between the Ledger and this combined set of Custody rows with the Join configured on the 4 fields that would've been concatenated in your previous solution (Fund, SEDOL, Payment Date and Net Amount). Then I've added a Unique tool to provide just 1 row per unique set of Fund, SEDOL, Payment Date and Net Amount values since I don't know the consequence of having multiple Custodies return the same Ledger entry.
![search multiple excel files at once search multiple excel files at once](https://cracksset.com/wp-content/uploads/2021/05/stellar-repair-for-excel-1.jpg)
Next, we'll Union all those rows together (since you've gotten them all formatted the same). Hence, attached dummy sample data which I created myself.įirst of all, I appreciate the amount of detail you've provided describing your scenario, it was very helpful to understand.įor your 6 custody files, we'll want to change the configuration to Output File Name as Field so that we can track which row came from which file. But should be in 6 different sheets as per custodies.ĭue to data confidentiality I could not send the exact data. The output must be in a single excel file.
![search multiple excel files at once search multiple excel files at once](https://i.ytimg.com/vi/-vP0kbuyVvY/maxresdefault.jpg)
#SEARCH MULTIPLE EXCEL FILES AT ONCE HOW TO#
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). 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. 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). 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.Hence, macro would combine the same for custody data as well on column 1 in remaining 6 sheets respectively (as there are 6 custodies). The custody statement will also consist of the same data – Fund, SEDOL, Payment Date and Net Amount.(Note that the date got converted to 44278) Once the value was received, the formula was removed to get a string value. Refer below snap from ledger sheet.įormula used to combine them was =B2&C2&D2&E2 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.Custody's statement are compared with our ledger to figure out the unmatched. Daily 6 different custodies send the statement in excel file for confirming the payments. Ledger would just reflect matched payments as matched rest are shown as blank. We have our ledger file in excel which represents all the matched and unmatched payments. The macro is currently being used to filter out matched payments. 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.