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]

Good morning @JoshuaElphee 

I had to do a bit of research into this but there seems to be a reasonably easy solution :D

So originally I thought you could setup the output tool to output multiple files but I think that only works for CSVs. For excel files you seem to have to add a formula tool to create a custom path. From there you modify the output to change the entire file path.

That said you can either:

1. Change the files to CSV

2. Do this simple workaround: Output to multiple files

They will both be easy to implement so choose either one. Have a great weekend!

[jdminton]

In order to have each output to a new workbook, you need to do it a little different for Excel files. Change "Append suffix to File/Table Name" to "Change Entire File Path" and add a formula tool to do something similar to the image below.