Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to combine multiple workbooks with different headers into one

dsevilimedu
7 - Meteor

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!

4 REPLIES 4
princejindal
9 - Comet

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
7 - Meteor

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
9 - Comet

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

agabima
6 - Meteoroid

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. 

Labels