Is this Summarize, Cross Tab or Transpose??

I think this is doable but I've spent hours and am not able to get what I want. Attached is the spreadsheet and on the first tab is how the input is formatted. I need to take a row of data and add to it the other related items. I want all the rates to show on one line for the person. The second tab is what I want it to look like. I'm sure someone can figure this out in 10 minutes. Please help!

example.xlsx

Workflow

Input

Output

Accepted answers

philipmannering

@ewassell

How about this?

It is Transpose and Crosstab.yxmd

All comments

philipmannering

@ewassell

How about this?

It is Transpose and Crosstab.yxmd

TheMattLeonard

This is definitely doable. It's certainly a little tricky since you need to do it in a few steps.

First, use a Transpose Tool. First Name, Last Name, StaffingSupplierSite, Transaction Type, StaffingSupplier, Start Date, and End Date will be your Key Columns. Bill Rate and Pay Rate will be your Data Columns.

Next, use a Formula Tool. Make a new column called Rate+Transaction. The Expression should be [Name]+"_"+[TransactionType]. This will create a new field thats a combination of the Rate Type and the Transaction Type.

Now we need to do a second Transpose. This time, your key columns will be every field EXCEPT TransactionType, Name, and the newly created Rate+Transaction field. Your data columns will be just the newly created Rate+Transaction field.

Finally, you can do a Crosstab that will get it to the format you want. Group on every field EXCEPT Value, Name, and Value2. Change Column Headers to Value2, and Select "Value" as the option for Values for New Columns. Make sure you aggregate by summing.

From here you can do whatever cleanup you need to do to the field names/orders via a select tool.

Hope this helps!

ewassell

I never thought of doing Transpose and Crosstab. I appreciate the responses so much! This will save me so much time. Thank You, Thank You!!!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703