So following on from the previous video, HR have kindly provided us the file of salary details. In the fall we have employee ID, their first and last name and their current position. We also have the business unit code, business unit description and current salary. Our manager has asked us to split this file into individual workbooks by the business unit code column. Each file will be sent to one of the senior managers in that business unit to fill in the forecasted spend. So at the end of this, we should end up with eight different workbooks.

We also need to include four new columns at the end of the file to forecast whether each employee will get a pay rise commission bonus and fringe benefits. So for step one, let's start by importing our HR file into our workflow. We'll go to the in house tab and drag in the input data tool and connect to our HR file 9.1 Hr file worksheet. For step two, we need to add four new columns to the end of our file. We'll do that by adding a formula tool into our workflow. So let's go to the preparation tab and drag in Formula.

Let's go to the Select column drop down to the left and add pay rise as one of the columns that we needed to add. We'll give this a data type of double with a size of eight and assign it a value of no as we want the senior manager in that business unit to fill this in. Will do There's three more times for commission bonus and fringe benefits. commission. Bonus and fringe benefits, Double, double, and double. For step three, we'll add one more column called file, and this will be the location of where we store our spreadsheets.

We'll give this a format of a W string, leave the size alone and type the folder location in double quotes along with the file name and sheet name. So I'll put mine in C Drive, educate workshop one, workshop one dot XLS x sheet, sheet one. Now here's the important part. Because we want to split the sheets by business unit, we're going to add the business unit code, in other words, be a one or two etc, into the filename. So we'll break up the text here with a double quote and we'll add the BU code in here. Bu code plus XLS x.

And for sheet name will also put the code. Let's add a browse tool with Ctrl, Shift V, and run our workflow. And we've successfully added our four columns here, and our new file column. Okay, so the final step is to export all of this into eight different spreadsheets. Let's start by dragging the output data tool into our workflow and connecting that to the formula tool. In the configuration pane, we'll go to file and save this as a spreadsheet.

We'll give this an arbitrary name. It's not going to matter because it will be overwritten by the file name that we created in the formula tool. Under output options, we'll select overwrite file if it already finds an existing one, and under take file table name from field. We're going to change the entire file path from here, and we're going to replace it with the file column we created in the formula tool. Once we've done that, we can run our workflow. And if we go to this folder here, we will verify if our records were written successfully.

And then we have our HR file has been split into eight different files, and it's ready to send to our senior managers to fill in