Microsoft Excel Help! (1 Viewer)

[cubd8]

Hello,

I'm trying to create a report from raw data, using a pivot, that is refreshable that gives me 1 view. Currently, the data that I am looking has worker names in 3 different columns, but I THINK I need to create a master worker column and then aggregate/count/display the various order status that this worker is attending to.

I tried to create an example below.

All of the data is in excel. I am looking for help in creating a formula that would assist in this creation.

As always, thank you for advance!!!

Here's my data:

Column AC titled: CS (Client Support):

That column will have the names of the Client Support:

Bob James

Sue Smith

Tom Jones

Column C titled: Order Status:

In Process

For Review

Available

**

These two columns are being used to create a pivot table view:

For example:

Client Support:     In Process  For Review  Available Total:

Bob James               3                  1                      1           5

Sue Smith                  2                 2                       2          6

Tom Jones                1                 0                      0           1

******************************

Column AD titled: Primary:

That column will have the names of the Primary worker:

Sue Smith

Julie Anderson

Bob James

Column AE titled: Secondary:

That column will have the names of the Secondary worker:

Bob James

Brian Stevens

Julie Anderson

**************

Here's the question:

How can I create 1 refreshable view, using a PIVOT that gives me ALL data for an individual worker?

For example, in the above example, Bob James has 6 orders, but also 1 as a primary and 1 as a secondary.

I need to use the CS/Primary/Secondary columns to create a master 'worker' field that captures all of the information from the above.

I want something like this:

For example:

                                                    CS                                                           

Worker:                 In Process  For Review  Available Total:       Primary:      Secondary:     Total:

Bob James               3                  1                      1           5                1                       1                  2

Sue Smith                  2                 2                       2          6                1                        0                 1

Tom Jones                1                 0                      0           1                0                        0                 0

Julie Anderson         0                  0                     0            0                 1                      1                   2

 

[cubd8]

Bumping for the day crowd

 

[Captain Cranks]

Just for clarity's sake, does a row of your raw data look like this?:


Header


CS


Status


Primary


Second


1


Bob


For Rev


Sue


Julie

 

[Captain Cranks]

If you convert your raw data to look like this, I think that would work:

CS            In Process       For Review       Available       Primary     Secondary

Bob             <blank>             Bob                <blank>         Julie             Sue

You can create columns in the pivot table using "pivot table calculated fields" for your totals.  

 

[cubd8]

If you convert your raw data to look like this, I think that would work:

CS            In Process       For Review       Available       Primary     Secondary

Bob             <blank>             Bob                <blank>         Julie             Sue

You can create columns in the pivot table using "pivot table calculated fields" for your totals.  

To your previous reply.

There are several columns, and the data for the CS, Primary, and Secondary are all different rows.

What makes this difficult is that there is specific information that I want to see for the CS vs the Primary/Secondary, but want everything in one view.

How do you do the 'pivot table calculated fields' ?

 

[Captain Cranks]

 http://www.contextures.com/excel-pivot-table-calculated-field.html

Where is this data housed and how are you querying it?

ETA:  I'm trying to understand what the raw data looks like and in what way is the CS, Primary, and Secondary data related from a database perspective.  Can you provide an actual 5 row sample?