Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

Using Alteryx to Combine Excel Templates

Highlighted
7 - Meteor

Hello,

 

I am new to Alteryx and I am having a little bit of trouble combining two Excel sheets. On one sheet, I have a complicated multi-tab calculation.

 

On the other sheet, I have three columns with the first one being the name. There are many different descriptions in the second column and then the third column contains dollar amounts. Here is an example:

 

Capture.PNG

 

In my first sheet, I have a tab which I can just paste this information from the second sheet into. Then the calculations on the other tabs on the first sheet will run based on the descriptions and pull the required information.

 

Is there a way that I can have Alteryx take all of the information on the second sheet, split it up by name and paste it into separate first sheets for each name and have the first sheet run the calculation?

 

This might not be as clear as I think and this is my first post so please let me know if additional information is required.

 

 

Highlighted
ACE Emeritus
ACE Emeritus

Hi,

Excel templates always get a little tricky, so I'm going to start by handling the sheetname piece first.  I suspect that Named Ranges might be what you want from an Excel Template perspective.

 

From an output Perspective, outputting to multiple sheetnames is fairly straightforward. Taking your example, if I assumed that we wanted each Name to have its own sheet (EG Craig gets their own sheet named "Craig"), You can configure this in your output Data tool using the options at the bottom.


Here's a quick example screenshot.  Basically you want to check off the "Take File/Table Name from field" option at the bottom of the screen, choose "Change File/Table Name" and pick the field you want this name to come from.  You can then choose if you want to keep that field in the output.  Note that you could also use a Formula beforehand to come up with your sheet names, which is great if these sheets are not 1:1 with the names on your list.

CommunityOutputChangeSHeet.PNG

Highlighted
7 - Meteor

Hi Claje,

 

Thank you for your response. I am essentially trying to have only Craig's information inputted into the Excel template, then have only John's information inputted into a separate version of the Excel template. The oupute I am looking for is to have the Excel template's third tab (output graph) provide me with three different pages, one for each name.

Highlighted
ACE Emeritus
ACE Emeritus

Assuming that your three excel template files already exist, you can use a formula to create a new field that will include the full file name/path for each of these templates, based on Craig/others

Something like

"\\yourfilepath\ExcelTemplate_"+[Name]+".xlsx|Sheet1"

Then you can change the output type in the Output Data tool example to "Change Entire File Path" and use your newly created Filename field.

Labels