Hi community! I am stuck on this workflow that I am trying to build and need assistance. I will do my best to simplify what I am trying to achieve. I would be so grateful if someone could take a look at this.
I created a main folder called Inputs, and within that folder, there are 2 folders – Statements and Customers (but customers folder is irrelevant for this, I do not need help with that).
In the “Statements” folder, there are 4 files (.xlsx). Each customer has two files (for 2022 and 2021, but in future the years will change, so 2022 and 2023 etc). Currently, I have “Statements_Seth_2022.xlsx”, “Statements_Seth_2021.xlsx”, “Statements_Pooja_2022.xlsx”, and “Statements_Pooja_2021.xlsx”. There could be way more than just these 2 customers in the future.
I also have a mapping file:
| Entertainment | Devices | Electronics |
| Food | Dining out | Dining expense |
| Food | Groceries | Grocery expense |
| Memberships | Total memberships | Membership total expense |
| Transportation | Uber | Ride Share |
Here is what I am stuck on. I brought in the mapping file above already in my Alteryx workflow. I also use a directory tool to bring in the Inputs folder and then use a filter tool since Customers and Statements will be two separate streams. ONLY Statement files connect to the mapping file. And here is how. Below is an example of one of my statement files. You can see the Sub Section always lines up with column A in the statement file. The line names are in columns B, C, and D (D in some cases). I need the workflow to have a macro/macros that goes through every file in this folder and looks at the line name and makes an extra column that adds the corresponding tag (Tag mapping) based on this mapping above and then outputs 1 workbook per person with 2 tabs (1 tab for each year). I do not know how to connect my table to the macro that handles the Statements. Every statement file is structured the same and only has one tab in it (2022 or 2021). I need a macro/macros because this needs to be able to go through as many people as there are in the folder. I do not know how either how to differentiate the files for the output specifically. Each file has the customer name and year in the filename and the tab name will always be the year (like 2022 or 2021).
Naming convention/output: Once I bring in the statements for the different customers, I need to update the names to have a similar naming convention. I want the convention to be “Summary_2022_Pooja.xlsx” and so forth for each person / year. I want the output to only be ONE workbook per person though. So Pooja 2022 and Pooja 2021 Statements go together in one Excel file, but separate tabs (2022 and 2021). It can be called "Summary_Pooja_Statements.xlsx".
The first table below is the statement file, and below that is an output example (below the red highlighted text). Feel free to copy and paste this into your excel file and make extra copies / rename them to mimic the 2022/2021 files. The structure is always the same.
A B C D E
| Sheet | | | | |
| 12/31/2022 | | | | |
| | | | | |
| Entertainment: | | | | |
| | Devices | | | 4,500.00 |
| | | | | |
| Food: | | | | |
| | Dining out | | | 1,600 |
| Memberships | | | | |
| | | Total Memberships | | 50.55 |
Here is an example output:
A B C D E F G H I
| Sheet | | | | | | | | |
| 12/31/2022 | | | | | | | | |
| | | | | | | | | |
| Entertainment: | | | | | | | | |
| | Devices | | | 4,500.00 | | | | Electronics |
| | | | | | | | | |
| Food: | | | | | | | | |
| | Dining out | | | 1,600 | | | | Dining Expense |
| Memberships: | | | | | | | | |
| | | Total Memberships | | 50.55 | | | | Membership total expense |
