splitting a workbook into multiple files based on a column value

Question

so i have a master file which i need to break up into multiple files (1 file for each unique store taken from the "Just Store" column in the Combined tab). but at the same time i want all other tabs intact each file.

then i want each of these files to be emailed to their store.

input file is attached.

Input file.xlsx

FauzK · Answer

Hi @danilang

Please help with my query

FauzK · Answer

Thank you so much. your workflow helps. i just have 2 more issues:

1. two of my tabs have a specific format and UI but i lose that when i runflow. e.g. of what that tab looks like. thsi is same Store SUmmary and District Summary

2. one of the tabs has a pivot and i want that intact based on the store. but i lose that pivot when i run the workflow.This is tab Combined Pivot.

please help in this regard. will highly appreciate it.

danilang · Answer

Hi @FauzK

Here's a simple but messy way you can do this using a batch macro.

The main workflow just reads the Combined sheet for the workflow and finds the unique store numbers which it passes to the control parameter of the Splitter macro.  The "First 3" sample tool is used to only output files from the first 3 stores.  Remove this to create output files for each store.

This is the batch macro.  A batch macro runs once for each item passed in the control parameter, the store number in this case.   Each horizontal line of tools reads a different sheet, filters for the current store number and writes the output to a file with the store number as a prefix and the correct sheet name.  The action tools outlined in green change the filters by updating the 7001 to what ever the store number is for the current iteration.  The orange action tool do the same for the Output Data tools changing the file name.  I've only got the connections from the actions tools that connect to the first row of tools visible.  The other connections are set to be "wireless", which means their only visible if you click on one of tools at either end.  Click on the second action tools to see the wireless connection between it and the filter in the second row of tools.  This is to avoid cluttering up the image with extra crossing lines.

The tools in the blue box are used to synchronize the output tools to ensure that each row is written before the next one starts.

This example only writes the first three sheets.  For the subsequent sheets, copy/paste the tools in the purple box.  Change the sheet in the Input Data tool, the filter column to use the proper column in the current sheet and the output sheet name.  Connect Output 2 of the previous Block Until Done to the input of the new Summarize tool.  Choose any Column to count and make sure to rename the counted column to "SynchroCount"

For more info about macros, check out the Interactive Learning series about macros.

When you feel more comfortable, you can make the macro more general, by passing in the sheet name to read and the column to use in the filter tool.  With this info, you can reduce to macro to just an Input Data, a Filter and an Output Data tool, with the various tool being configured by the 3 input parameters.  In your main workflow you will need a mapping file files that specifies which column to filter for each sheet.

For the email requirement look at the EMail Tool Mastery and this article on how to add an attachment to an email.

Dan

FilesByStore.yxzp