"Joining" multiple xlsx files - need 1 column from 50 files to create 50 columns in 1 file

[sshereef001]

Hi all,

I've been scouring the forums to find a solution for "joining" fifty different excel trial balance workbooks (with one sheet each) together onto one sheet/workbook.

Context: each trial balance workbook has one sheet, and the columns in each sheet (across all workbooks) are identical, though the rows can differ. I need to append one column from all 50 trial balances onto one sheet (i.e. F1 is trial balance account name e.g accounts receivable, F2 is the column I want from workbook 1, F3 is that same column from workbook 2, F4 is the same column from workbook 3...so and so forth). 

Most of the solutions I have found use some form of batch macro (to mitigate different schemas across the workbooks), and/or directory input + dynamic input. Setting aside the formatting issues, this results in a "union" where all fifty workbooks are stacked on top each other -- I am trying to append a particular column from 49 different workbooks to one workbook (i.e. combine the 50 files). 

I know the above is wordy, but hopefully lays out what I am trying to do. 

I am attaching 5 sample files: 4 individual files that I would like to join together, and a consolidated file that shows the output I would like to generate via Alteryx. To mimic my actual data, the rows in each of the 4 individual samples differ, and the output file will show null values for these missing data points.

Thanks in advance for all of your help and advice -- this has had me spinning my wheels all day! 

Best,
Shef

[T_Willins · Accepted solution]

Hi @sshereef001,

Based on your naming conventions of your files, you can use the Input Data tool to help segregate an organize the data.  By Outputting the File Name as A Field (image 1) you can use the FileName to set up the data for a Cross Tab tool.  The Text Input tool is to provide a framework for the data to put the Accounts in the right order as the Cross Tab tool will alphabetize the data and not all Accounts are in every file. As long as there are not schema issues, this can all be done without a macro.

[sshereef001]

Hi T_Willins,

Thank you very much for this solution -- it works! The game changer was understanding that every line item needs to be linked to an identifier (i.e. file name/path), which acts as a field to aggregate the entries, and be converted into 'columns' in the cross-tab. 

So too was the step to union after joining -- that also helped orient the data as I needed it. Like you said, it also helped that the files were named similarly and had the same schemas. 

Thank you once again -- this was extremely helpful.

Shef