I need to filter and create a new output file for unique "inputs" within one column(EXCEL)

[JoshuaElphee]

I need to take a workbook with many columns and rows, and perform the following actions:

1. Create a new workbook based on a specific column "Job" - this requires numerous "output workbooks", one for each "Job" identified
2. For each of these workbooks, I need to total the value in several of the existing columns, pull in an existing workbook's data for the "Job" and add this as a new row, add a total to the bottom (current "Job Total" plus "Previous Job Total"), and provide a row which shows the percentage of "total obligations" for each column.
3. Take the new "Total" for each column under each job and develop a new workbook which shows "Job XYZ" with $XXX total for each column. (Separate output file)

I am attaching an example spreadsheet:
-"Sheet 1" represents the data pulled for a specific period of time
-"Example Prev. Data" represents the "Previous Job Totals" as referenced in step 2 (additional input file)
-"Example Job ABC1 output" represents the spreadsheet which would be created for "Job" "ABC1" from the final column in sheet 1
-The workbook noted in step 3 above would need to be similar to that in sheet "Example Prev. Data", just with new totals

I am absolutely stumped on where to start. I can not figure out how to create numerous output files (each of which should be saved with the name referencing the "Job" field input.... much less pull in the "totals" specific to one job after that job has been pulled into a new workbook.

At the end, I will be doing this for 80+ "Jobs"; manual efforts are not an option.

NOTE: within the example workbook, the totals shown (and percentages) may be confusing; I assure you this represents the current need due to their nature. Each "Area" should be a percentage of "total obligations"; when "Obligations" are spread across multiple Areas, the full obligation value is shown in both.

Any assistance in the logic and workflow is greatly appreciated!

[tristank]

Hey Joshua!

I'm not 100% sure what you are trying to do but as it relates to outputting separate 'workbooks' based on a given column you can do a group by in the output tool and append the job name to the end of the output name. As for the other stuff I think I may need more context / have to see a little sample of your intended output. At a high level, my guess is you may want to take the initial outputs of the different jobs and dynamically feed them into a new workflow that can do the desired calculations in a batch macro or something :)

Hope this helps and if not apologies!

[tristank]

*Note in the picture below the outputs are grouped by the region and the region name is appended to the end of the file*

[JoshuaElphee]

Thank you - I will try this as a first step!  You are correct, the need is for a separate output for each "Job" (workbook for ABC1, separate workbook for , DEF21, XYZ7, etc.)

Once these have been split, and are in separate workbooks - I pull in the "previous totals"; basically I need to read the row within the "example Prev. Data" workbook for "ABC1", and paste it within the ABC1 file.  

The third page within my attachment shows the final result:  (total of $ for numerous fields in several columns as "Current Period Total", the $ under the same columns as pulled from the separate workbook named "Example Prev. Data"; I then total these and list the row as "CURRENT TOTAL" within the same workbook.

If a dynamic input is required, can you advise on how to start on such a need?  

[tristank · Accepted solution]

At a high level, it sounds like you need to output a bunch of different files based on the job, then bring them back into Alteryx to append the previous total data to the job files. If the process will be the same for each data source, what you can do is read them all into a new workflow using wildcard syntax to pull them all in.

If the process is the same for each job data source, you can use a batch macro to run them through the workflow and output them one at a time. That way you only need to make the workflow once and then the batch macro will take care of the rest.

I highly recommend checking out the super easy batch macro training provided by Alteryx and seeing if this will help you efficiently solve your problem. If this isn't what you're looking for, feel free to reach out again and we can work through the problem.

Have a great weekend!

Tristan

[JoshuaElphee]

Tristan,  Thank you so very much; I will take this training and report back as soon as possible.  What you describe appears to be exactly what I need; and I greatly appreciate your direction to the training. If you dont mind, as I hit snags I will reach out to take advantage of your expertise.  

Have a great weekend.

[tristank]

I'm more than happy to help @JoshuaElphee 

Reach out at any time and keep us updated!

Thanks,

Tristan

[JoshuaElphee]

@tristank I have run into an issue where this output is adding a new sheet vs. creating a separate workbook - I am unable to identify where the error may be/ if it is possible to output multiple workbooks vs. multiple sheets in one workbook.

[tristank · Accepted solution]

Hi @JoshuaElphee hope you are well

Can you show some pics of what is happening so I can have a better understanding of how the output is setup?

[JoshuaElphee · Accepted solution]

@tristank  absolutely.  I set up the output as follows:

This resulted in outputs of one excel file with multiple sheets (vs. multiple workbooks with one sheet)  I am not certain it is possible to save multiple workbooks through this manner based on the output options I am seeing:

Within each sheet, the data does appear as intended (below is an example only):