Hi all,
Relatively new to Alteryx and looking for some guidance on how to go about doing the below! Hopefully there's a Pro for who this is a breeze!
I have 2 Excel files; a working file and a reference file
Working File:
| Working File (Initial) |
| Status | Name | ID | UnitCode |
| Active | John Smith | | LDA |
| Pending | Sarah Jane | | LDA |
| Complete | Paul Crow | | LGHC |
| Active | Becca Torrini | | HYT |
| Pending | YT Smithson | | HYT |
| Complete | Georgio Yan | | LGHC |
Reference File:
| Reference File (Unchanged) |
| Activity | Name | ID | UnitCode |
| Add | Smith, John A. | 01234 | LDA |
| Out | Jane, Sarah | 768 | LDA |
| Out | Smithson, Yan T. | 00678 | HYT |
| In | Torini, Rebecca | 892 | HYT |
| Out | Yan, Georgio | 00765 | LGHC |
| Out | Crow, Paul | | LGHC |
What I want to do is:
If the Status in the Working file is "Active" AND the Activity in the Reference File is either "Add" or "In", I would like to lookup the Name and UnitCode to see whether I get a match.
If there is a match, then I would like to do 3 things:
1. Stamp the name as it appears in the Reference file, into the working file
2. Stamp the ID that exists in the Reference file into the Working File
3. Change the Status in the Working File from "Active" to "Complete"
The Working File will then look like this:
| Working File (Updated) |
| Status | Name | ID | UnitCode |
| Complete | Smith, John A. | 01234 | LDA |
| Pending | Sarah Jane | | LDA |
| Complete | Paul Crow | | LGHC |
| Complete | Torini, Rebecca | 00892 | HYT |
| Pending | YT Smithson | | HYT |
| Complete | Georgio Yan | | LGHC |
There are 3 nuances.
1. The name may not always match exactly, as in the above examples.
2. The UnitCodes are non-unique, but serves as a good check to see if the name should have matched.
3. The ID should always be 5 digits long, so if the Reference file only has a 3 digit ID, I would like to add 0's in front to ensure it reaches 5 digits.
Any help would be hugely appreciated!
