Combining and reconciling data from (1) Different sheet in (2) Different excel workbooks.

I have 4 different files for each month that I need to combine and reconcile. All files have a common field from which I can pull data from each file and create a reconciliation for each month

I need help with created a YTD report which has 'Monthly recon combining all 4 files' for each month stacked to each other.

So I need to know how alteryx can pull all 12 months file for each file type (12x4=48 files) and create a Monthly recon and stack it next to each other

I tried using Input directory to read all files (I created a folder for each type of file) and then applied Dynamic Input. But I am hitting a wall after that as Dynamic input is not able to read all data in all files. I selected one file as example for the template and ask it to pull data but its not working. I want to be pulling data from one specific tab from each months file.

Workflow

Accepted answers

All comments

alexnajm

Something like this may help! The Ultimate Input Data Flowchart

Directory --> Formula to add "|||sheetname" to each FullPath (whatever your sheet name is) --> Batch Macro (downloadable in article) to read in sheet from all files

TheMattLeonard

" I want to be pulling data from one specific tab from each months file."

This is the key. Is that tab name the same for each month and for each file type?

Honestly though, regardless of your answer, I would approach this with a batch macro. Your directory tool needs to provide the filepath (up to and including .xlsx) and then you can input the sheet (tab) name you want via a formula tool. If the sheet name is the same in all instances, then that can just be put in quotes in a formula expression. If it varies, that will require additional logic.

To create a dynamic input macro, start a new workflow and use an Input Data tool to input a single instance of the file as a template. I like to include the full filename as a field to keep track of things. Input two control parameter tools and attach them to the input tool. Label one Control Parameter as FileName, and the other as Sheet Names. Within the Action Cards that should pop up, select the filepath, then check the box at the bottom to replace a specific string, and then edit that string to be just the filepath, or just the sheet name, depending on which control parameter it is attached to. Also connect a Macro Output Tool to the Input Data Tool. Before saving your macro, go to ViewàInterface Designerà Properties, and then check “Auto Configure by Name”.

Back on your main workflow, right click the canvas, select Insertà Macro and then navigate to the macro you just created. From here you should be able to connect your list of filepaths and sheet names to the macro, configure it so those two fields match up to your control parameters, and from here you should be able to run it and combine all 48 files.

DeepakN

2 main challenges is one That after combining all periods , how do i spit out monthly recons between the 3 file types. Does it require to mark each data set to which month it belong so that later it can be filtered and reconed for...and secondly, one set of file have 5000 line items of which i need 500 with a single column filter, but this is for all 12 periods, so before pulling all 12 monthly files in dynamic input, can this filter be applied

alexnajm

This is a much different question than your original one - has the original query been resolved?

DeepakN

My Bad, I should have included the complications. this is part of the origina query

alexnajm

Please let us know if the original query of reading in multiple tabs has been resolved first - once it has, I would post a new question about your additional query. I would also include sample data so that other Community members can understand the exact ask!

TheMattLeonard

In regards to your first question, I'm assuming those datapoints aren't contained within the data itself? If so, that is where including the Full FilePath as a field in the Input Data Tool in the Macro Workflow comes in to play. I assume you can parse out the Month and FileType from either the Folder Names, the File Names, or the Tab Names? That is how you "mark" the data. The string formulas could get somewhat complicated if Month is located in the middle of a folder structure, but it is accomplishable.

In regards to your second question, I would accomplish this filtering within the macro, so in the macro workflow, in between the Input Data Tool and the Macro Output Tool, insert your filter and have it do what you need it to do so that the filtering will happen to every file. You could even attach a second Macro Output and attach it to the F output of the filter if you're so inclined

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703