Combine Multiple Files Based on Sheet Name

[macst245]

Hello, I have a folder of different output files for different months (I.E. Jan, Feb, Mar, Apr) and I want to automate the process of inputting certain files into a workflow.  These files all have the same schema and same sheet names.  I need to compare two separate files that are 3 months apart so if we are working in April, I will need to compare that data to the Jan data.  I will then need to run the same workflow the next month so in May, I will need to compare that data with the data output from Feb.  

After inputting those two files into Alteryx automatically, I would like to compare the data on the two spreadsheets based on the same named sheet from both files to create two new columns of data. (I.E. I would like to create a new column called Direct Send Quarterly Metric where on the April spreadsheet we take the Directly Send for each category divided by the total Directly Send and then take that total and subtract it from Jan Directly Send divided by the total Directly Send).  

The second column of data to be created is similar to the first where on the April spreadsheet we would take the Direct Received for each category and then divide that by the total Direct Received number, then we would subtract the Jan Direct Received divided by the total Direct Received and that would give us the new metric.  

This would need to be done for each sheet on the excel files where we compare the data from the two spreadsheets based on the sheet name (Company A from April would need to be compared and have the new metric created based on Company A sheet from Jan spreadsheet.

Once the new metrics are created for each spreadsheet, I would like if all the data could be combined into one spreadsheet with the new columns as part of the data set and the sheet names from the original files carried over into the new output file.  

I have attached four sample excel files as an example of what would be in our file that we get our data from.  Hope this was not too confusing, but if I can clarify anything, please let me know.

Thanks

[RolandSchubert]

There @macst245,

if I got you right, there are 5 steps needed:

1. Identify the "corresponding" Excel file to your current month. This could be done using a Formula tool (use DateTime functions to find month -> previous month, format the date to meet the filename rules and create the filename)

2. build a list of sheets in the files (a macro needed, hand over the file names to the Input Data tool and retrieve only sheet names)

3. read data (Dynamic Input or macro)

4. split between total values and detail rows

5. transpose both data groups and join totals to detail rows, calculate shares and differences

The attached workflow illustrates the process, but is not completely finished (e.g. no handling of n/a values).  Let me know what you think.

Best, Roland

[macst245]

Thanks for the help, I am running 2021.1.3.22649 so I wasn't able to download the package.

[RolandSchubert]

Hi @macst245,

you can extract the files using e.g. 7-ZIP (download available on 7-Zip) and modify the version by editing the yxmd/yxmc files to your version using a text editor (e.g. Notepad ++).

I added the modified files, you should be able to  use the workflow/macros with 2021.1

Best,

Roland  

[macst245]

Thanks so much for your help with this, I just have a question on what inputs you are using for the Macros.  Are you using Description or Filename for the first Macro input and for the second are you using Sheet Names or FullSheetNames?

[RolandSchubert]

I use Filename for the first and FullSheetNames for the second

[macst245]

Thanks for that, I am still getting an error however as shown in the screenshot below.  

[RolandSchubert]

Did you adjust that directory I used to create the filename in the Formula tool? 

[macst245]

Yes, I replaced your file location with the location of the files on my network's shared drive.  

Also, is there a way in this workflow you created for this to pull different files depending on the month when this is run?  I.E. If this is run in May, that it would automatically pull May's excel file and then also February's excel file, then the next month without editing the workflow, it would pull June and March's excel file? 

Thanks again!