How to combine multiple workbooks with different headers into one

[dsevilimedu]

Hello everyone,

I am a new user and this probably is a basic question.

I have 6 excel files, each having only one worksheet but the headers in all these worksheets are different except for the column "Project Number". I would like to start with file 1 with the column project number and look up values (I use function vlookup in excel ) from the other 5 files that matches to the project number listed in the file 1. For example: File 1 has the project number 12345, file 2 has a department id A123 for the project 12345, file 3 has an amount spent $500 for the project 12345 an so on. The output I am looking for is as below in one excel file

Project 12345

Department id A123

Amount Spent $500

Thank you very much for your assistance on this!

[princejindal]

Hi @dsevilimedu 

Just check if this workflow helps you. You will have to configure the summarize tool, text to column tool, and select tool as per your requirement. The first macro will read the sheet names and the second one will generate and combine the outputs. You can easily tweak the output in the format you want using the Transpose tool.

Hope this helps!

[dsevilimedu]

Thank you @princejindal for the solution. 

Since I am new to Alteryx I took the long route to create a model that I can understand and explain to others. After reading so many useful articles here in the community, I ended up using different tools: Join multiple, formula and summarize tool to create the one I visioned. I am so proud that I created my first Alteryx model :)

Thanks again for your contribution and your time. 

[princejindal · Accepted solution]

@dsevilimedu , I am glad you found a solution, and found it fun solving the challenge! 

[agabima]

Hi,

I tried to run your workflow but how can I specify the sheet name? I have multiple excel files with common sheet name and with some similar headers.