Parsing data from one row into two

Khari Shiver

Hello,

I have a grid in Smartsheet with two different types of sales data per employee. As there are different payroll codes for each type, I am trying to figure out a way to either use the pivot app or a report to create one or the other that would create a second line with the necessary data when needed.

For example, if an employee has sales in region A and B and both data is in a single row, I would like to create a pivot that would create a row for the data in region A as well as a row for for data in region B. The sales data for regions A and B are in different columns; I'm just not sure how to approach splitting it.

Thank you,

Khar8

Julie Fortney

Hi @Khari Shiver. Could you provide some additional detail on why you need each region to be on a separate row? If you could provide that context, I could probably come up with a simpler solution, but I do have an idea based on the info you provided:

If you have Data Shuttle, you could set up two workflows, one for Region A and one for Region B, and set them up to create a row for each region with only the columns you need, on a separate sheet. I can give more detail on this if you'd like.

Khari Shiver

Sure.

I would like to extract portions of the data from the Smartsheet to create the file that we will submit to payroll for processing. Because there are different payroll codes for payments for sales figures from both regions, and some employees have sales in both, I need to have the amounts on different lines.

I've attached a file with two tabs. The first contains sample employee data while the second contains an example of how I would like the data to look for the payroll file.

I do have access to data shuttle which I had not considered as a way to solve the problem. Please let me know if this additional context/data gives your additional ideas.

Thank you,

Khari

example 12.28.2022.xlsx

Julie Fortney

Based on what you've described, I would do one of the following. Data Shuttle could still be an option if one of these doesn't work, but I played around with it a little and I think it's going to get pretty complicated.

• Two reports, one for each region. This would require no maintenance on your part:

• A separate sheet with cross-sheet formulas to pull in the payment amounts based on the employee ID and Payroll Code. This would require some maintenance on your part when you need to add or remove employees.

Here is the Employee Data sheet I created:

Here is the Payroll File sheet:

The formulas for the Commission Amount column are as follows:

For COMM-A Payroll Code rows: =SUMIF({Employee Data - ID}, ID@row, {Test - Employee Data Region A})

For COMM-B Payroll Code rows: =SUMIF({Employee Data - ID}, ID@row, {Test - Employee Data Region B})

Khari Shiver

I understand and appreciate your work on my question. The challenge for your solution lies in the data set being dynamic specifically with employees being added and possibly removed. The manual addition and removal from the employee data sheet would prove to be too cumbersome.

I think my approach is going to be to create the two reports for region A and B, export them as Excel files, and combine the data into one Excel spreadsheet. It's not elegant but it works.

Thanks again,

Khari