Outpout columns to different sheets of excel files

Hi Community,

I want to get some help from you in getting an ouput from alteryx.

So, actually I have a Scorecard file that has A - Z columns in which first 5 columns has data on which employees are judged and rest 21 columns have employee names on headers and the scores in below fields, which means that in Colum F, the scores of Enployee A is filled, in Colum G the scores of Employee B and so on.

What I want is an output in which I get all employees scores different sheets or files which includes just their data. However the first 5 Columns must be there for all output. I means first 5 columns then the Scores of tjat Employee. So in total their would be 21 sheets or files.

Please community, do your magic and help me get a solution..

Thanks in advance.

Input

Designer Cloud

Output

Batch Macro

Help

Accepted answers

jdminton

@avisingh96 here is the workflow as well as the package. It wasn't clear if you still wanted the results in a separate workbook for each so I included an output that has the employees separate and an output that has them in the same workbook with different sheets for each. Please let me know if this works for you.

Edit: I've included the formula here as well. The key is to do the formula before exporting to Excel.

ReportByEmployee.yxmd

ReportByEmployee.yxzp

All comments

hellyars

@avisingh96

Have you tried using the Output Tool ...

and check the Take File / Table Name From Field option set to Append Suffix to File / Table Name and set Field Containing File Name or Part of File Name to the desired Field (it sounds like name or employee id etc.) -- see bottom half of image below

Screenshot 2023-06-10 164626.png

geraldo

@avisingh96

if you send an example of the spreadsheet data it could help with your need

Yoshiro_Fujimori

Hi @avisingh96 ,

With some assumptions, here is a potential solution.

I hope it helps.

Input Data

I assumed the input data would be something like this.

Workflow

Output Data tool above creates one Excel book with 21 sheets.

Reporting Tools below create 21 Excel books with some header information.

If you prefer this, you can activate the container.

( I could not figure out the way to make it to one book.

If any other community member knows the solution, please let me know.😉 )

Output

ReportByEmployee.yxmd

avisingh96

Hi,

Thanks for replying.

I tried that way, but in that the sheets are getting named on the data in 1 particular column. But I want the sheets to be named on columns.

I tried to transpose it as well but it ain't happening the way I want.

avisingh96

Hi Yoshiro,

Thanks for replying.

Yes this is the exact way I want my output, though I guess all are in 1 execl files in different worksheets.

I will be trying out this and let u know if it works for me.

Really appreciated for the help.

avisingh96

Hi Geraldo

I will try to do so.

Thanks

jdminton

You can create separate workbooks using the attached method. The key is to naming the file and replacing the entire path in the output tool.

ReportByEmployee.yxzp

avisingh96

Hi Everyone,

I have created a sample file in which first Sheet is the Sample Input files and rest all sheets are output which is created on the basis of Emp names.

Please help me in this.

I am able to create different sheets that contains scores of each employee and the first 4 columns of the Scorecard template (Thanks to Yoshiro). But I just want to know, how I can run a formula at once across al sheets so that a new column is added which will show the points the employee got if they achieve the target.

Sample Input,Output.xlsx

avisingh96

Hi @jdminton

I am able to create different workbooks now. All I need is to run a formula in all sheets at once. Can this be done with multi field formula. I am not sure because input file is running only 1 sheet at a time and if I use dynamic input. All data is getting collated to one.

avisingh96

Hi @Yoshiro_Fujimori @hellyars @geraldo

jdminton

Edit: I've included the formula here as well. The key is to do the formula before exporting to Excel.

ReportByEmployee.yxmd

ReportByEmployee.yxzp

avisingh1996

Hi @jdminton

Thanks a lot for this solution. This one helped and I guess my project will be completed soon. Thanks again

jdminton

Good to hear @avisingh1996 . Can you please mark the solution so others will know it was resolved?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703