Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

Create separate files from a Master excel file for each rows

georgechacko
7 - Meteor

Hi All,

 

I have a Master File abc.xlsx with sample data given below:

 

Name     Age    Mark1      Mark2   Mark3      Total

a              19      50             60         70          180

b              18      80             40         50          170

c              19      70             60         60          190

 

I have to create 3 files like given below

1. File Name a.xlsx

Sheet Contents:

Name     a

Age      19

Mark1   50

Mark2   60

Mark3   70

 

2. File Name b.xlsx

Sheet Contents:

Name     b

Age      18

Mark1   80

Mark2   40

Mark3   50

 

3. File Name c.xlsx

Sheet Contents:

Name     c

Age      19

Mark1   70

Mark2   60

Mark3   60

 

Appreciate for any help with an Alteryx workflow.

 

Thanks in advance

 

Regards,

George

 

 

6 REPLIES 6
kelly_gilbert
13 - Pulsar

Hi, @georgechacko - you can accomplish this with the Transpose tool, then by grouping your output in the Output Data tool.

kelly_gilbert_1-1628719115651.png

 

First, use a Formula tool to generate a custom file name for each Name.

kelly_gilbert_3-1628719298595.png



Next, use the Transpose tool to transpose the columns into rows, grouping by the output_file name.

kelly_gilbert_0-1628719104496.png



Finally, use the Output Data tool to output to Excel, taking the filename from the field you created. This will group all of the "File Name a" rows into one file, all of the "File Name b" rows into another file, etc.

 

kelly_gilbert_2-1628719200324.png

 

 

(Note, in the future it would be helpful if you could attach the sample Excel file to your message. You can also attach the Alteryx workflow to show us what you have already tried!)

georgechacko
7 - Meteor

Hi Kelly,

 

It is absolutely working fine. Thank you so much...

 

Regards,

George

georgechacko
7 - Meteor

Hi Kelly,

 

Thanks for your earlier help. I have one more question that how can I format the cells background color as well?

 

Current output data look like below:

Name        a

Age          19

Mark1      50

Mark2      60

Mark3      70


Column A background color is light Green 

Column A background color is light Blue 
Required format is

 

Thanks in advance

Regards,

George

atcodedog05
22 - Nova
22 - Nova

Hi @georgechacko 

 

Can you provide more info on the background color requirement.

georgechacko
7 - Meteor

Hi,

 

Thanks for the reply

 

I have a master file. i have bifurcated the file based on each row and created as separate file. I have to add the formatting also in the data as

 

A1:A5 - background color - light Blue

B1:B5 - background color - light Green

 

Thanks in advance,

George A C

atcodedog05
22 - Nova
22 - Nova

Hi @georgechacko 

 

Here is how you can do it. You can do it using reporting tools.

Workflow:

atcodedog05_0-1631533960026.png

 

By using groupby in table tool I am converting data into multiple tables based on output file. I am changing color using column rule. And based on output file i am outputting to different files.

 

Hope this helps : )

 

Labels