Hi, I am currently planning to generate multiple excel files and tabs based on two columns. The idea is illustrated as below: Does anyone know how to generate output as above? Preferably with the naming as well (i.e., not the default name of the output sheet e.g. Sheet 1, Sheet 2 etc.). Thank you in advanced !

Hi !

I have solved the issue. The first layout grouped by bookstores and genre, however, this would lead to separate excel files, but all the tables are included in only one tab (i.e., Foyles.xlsx has a tab that consists of tables for Mystery, Horror & Romance).

Hence to get around it, I have included another layout tool that only grouped the table by bookstores. This would separate the genre tables to different tabs.

Screenshot 2023-05-25 133952.png

I have attached the workflow for your reference. Really appreciate everyone's help on this ! Thank you !

Multiple Excel Files & Tabs.yxzp

All comments

You need to adjust the file path, but this should do:

New Workflow5.yxmd

binu_acs

@nadiahghzli One of doing this

Excel_Tabs_230523.yxmd

Thank you so much for replying to this post ! However, I am unable to proceed with the suggested methods as I am dealing with a very complex data (e.g. numerous input). I have attached an example of the workflow and sample data. Currently, the workflow would generate 2 excel files (i.e., Bookmongers & Foyles) but only one tab that consists of all the genre.

I need to generate excel files based on the bookstore names (i.e., Bookmongers & Foyles) and a maximum of 4 tabs (i.e either or combination of 4 genres).

Example:

Screenshot 2023-05-24 153317.png

Appreciate if anyone could assist me on this. Thank you !

Multiple Excel Files & Tabs.yxmd

Multiple Excel Files and Tabs.zip

I am not able to rebuild this working as i dont understand what is your macro trying to achieve. I am not sure I quite get it. The first filter always filters out everything as it seems.

Also to send workflows much easier:

This will save a ".yxzp" a packed workflow including all data.

Yes, I can open the workflow. I don't understand why you are using this macro - This macro is what is not functioning correctly. Without understanding it I can't fix it. Your first filter always filters out my input Excel file path (attached screenshot). What is this filter doing, and why and what is with the next one(s)?

If you just want to separate Excel files, you can use something like

CONTAINS([Path],".xlsx")

But overall, what should the macro output? Can you maybe give me an example of what you want there?

Hi, FrederikE !

The macro's purpose is similar to a dynamic input tool, however, the input files doesn't need to be on the same schema. I have used the macro to combine the same tab between two excel files (as the two tabs has different set of columns), which would then generate the following outputs:

Screenshot 2023-05-24 181058.png

Screenshot 2023-05-24 181557.png

I have attached excel files (i.e., manually prepared) that consist of what I am planning to generate from the overall workflow. Thank you !

Bookstore - Bookmongers.xlsx

Bookstore - Foyles.xlsx

So this got quite messy, I am not really able to figure out what you do and why you do it, so I restarted it. I have provided a workflow that reads all Excels including all sheets and unionizes all the data.

I would recommend a separate process for non-excel files to not mix this logic up (F Output of the 1st filter) and then union it later on.

Main Flow:

Macro to read sheetnames:

Macro to read all Sheets:

Workflow.yxzp

Hi, FrederikE,

Thank you for the suggestion on the input ! However, I would like to separate the table between the tabs "Bought" and "To Buy" as I would like to maintain the columns name and not unionizes the data. (please note that the info provided is a sample data and I am currently working on a very complex data).

Furthermore, appreciate if you could assist me on the output part of my workflow as I am currently stuck on that. I would need to generate excel files based on the bookstore names (i.e., Bookmongers & Foyles) and each files contains a maximum of 4 tabs based on the genre.

Screenshot 2023-05-25 094250.png

My apologies if I am not clear. This is the third day that I am using Alteryx. Really appreciate your constant help !