Outputting to Multiple Formatted Excel Files

Hello,

I have an Excel file with A) Large Tabular Dataset, A pivot table on another sheet referencing that dataset, and C) Several other "template" sheets that reference the pivot table and summarize its data. Essentially I'm trying to create copies of that file that only include a subset of the full dataset. The end goal would be for every salesperson to have a copy of the file that only includes their subset of data. Then people would just have to refresh the pivot when opening their file to have a custom version. I've figured out how to split the source data into subsets with the reporting tools (i.e. Basic Table, Layout, Render), but that just outputs file with one sheet that is a data table. I lose the pivoting and everything else that references the pivot in the original file.

This would be a game changer if anyone can help out. Anything you can offer is great. Thanks!

Reporting

Common Use Cases

Preparation

Accepted answers

All comments

binu_acs

@CHTbedelschein Try blob tools

https://community.alteryx.com/t5/Engine-Works/The-Blob-Reporting-That-Isn-t-From-a-Horror-Film/ba-p/970280

jrlindem

Without getting too hung up on the classic excel pivot'ing functionality, I think you've essentially got all the building blocks detailed in your ask to get a dynamic workflow that outputs multiple files with tailored information based on splices that leverage the salesperson_id...

I might encourage or challenge you to think outside of the pivot box and see what you can do within the skills you've already discovered for this. Meaning, I think you're already there 😉

If you need more support on dynamically creating multiple files/tabs, there are lots of articles and discussions on that.

EKasminsky

Seconding @binu_acs on this. Use blob tools. I was intimidated by them at first, but I am glad that I learned how to use them. Also curious if you want to use Power Query to seperate files out or even a VBA macro to do so.

CHTbedelschein

Thanks! I'm reading @binu_acs article on blob tools now. I was hoping to avoid power query and VBA Macro altogether. In an ideal world I'd be able to input my master file and the workflow would output versions of it that only include subsets of the data, without having to do anything else to the output in Excel

CHTbedelschein

Thank you! I'm looking into this as a possible solution. Just to confirm, the blob tools can take a single file and output multiple copies of it, while maintaining the way the files work?

CHTbedelschein

Even with the tailored subsections of the data, I'm unsure how to solve the problem. I lose all the dynamic functionality, so just end up having to copy the parceled data and paste back into the source file to make a functioning copy.

Are you saying there's a way to output data in the same file as the template? Even if I remove the pivot and modify the formulas in the template to reference the raw data directly, my output separates the source data from the template I need it to feed. I'd still have to copy the data from my output, paste into the backend of the master template, and save a copy. Some additional direction here would be helpful.

caltang

@CHTbedelschein I read your question and took a pause.

You are already doing it via Excel and you are just slicing the raw data that the pivot and template files are calling from. Because of the formularized nature of the pivot and the template files, you will need to ensure that the path it is calling is the sliced raw data set for each of your salespersons.

The blob tool makes exact replicas of your files without changing the inside content at all, it just makes it easier to replicate templates (with formulas etc all intact).

I would suggest instead to first slice the data in Alteryx, then pivot via Alteryx, create copies of the templates with the blob tool, and then output to the templates with Alteryx (preserving their colours, template styles etc.).

Otherwise you will have a difficult time manually changing everything after the blob tool... which defeats the purpose of automation.

Gaurav_Dhama_

Check out this blog, it covers all the possible ways you can get formatting in excel.

A Guide to Formatted Reports in Alteryx - Alteryx Community

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703