Really need help with macro and output (differentiating two years)

[slaurel]

Hi community! I am posting again because my last post was flagged as spam, and it got pushed to bottom. 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

[Amit_G_Limbasia]

Dear @slaurel 

Thank you for sharing your Task and Get solution.

I've implemented it and found it useful.

To achieve dynamic naming and customization, I utilized regex expressions to extract and reload the data as needed. The solution was completed in two steps, carefully executed using the "Block until Done" method.

I've attached both the input file and the workflow with macros for your review. Kindly examine the workflow to ensure it meets your requirements. If everything aligns with your needs, please mark the issue as resolved.

Thank you again for your contribution.

[slaurel]

@Amit_G_Limbasia Hi! Thank you for providing this. It was helpful, but it does not go through all of the files and output how I would like. It only outputs Amit 2021. 

[apathetichell]

outer workflow - directory tool - formula tool to extract the name column from the filename. -> macro 1

batch macro 1> takes in full workstream and uses name and filename as two control parameter. 

1) input tool takes all sheets and is uptaded by filename action tool from control parameter 1

filter tool filters on name for control parameter 2.

this isolates all of your files with the same name.

formula tool replaces "<List of Sheet Names"> with  the sheetnames for the file.

-> files feed into control paramater on input side of batch macro.

batch macro 2 -> simplifieid excel input updated 

mapping file maps data here.

name column feeds to formula tool to make name column sticky.

macro output  feeds to macro 1

interface designer has this in union Name mode.

batch macro 1-> output anchor from batch macro two feeds to formula tool.

this creates a new summary file name with tab based upon name field.

output data outputs to new file.

[Amit_G_Limbasia]

Dear @slaurel 

I hope you have use same directory and contain all the files as when I have run the workflow it giving me all 3 file output with _1 and also create a summary file with 3 name respectively. Use input my reference and check once let me know will it work properly.

Please change the file location if needed somewhere

Thanks