Summarized Pivot Report - Need Help Creating

[bdav1216]

Hello everyone,


I am new to this forum and am looking for help in accomplishing the below. I am not an excel expert so will probably need more than most.


The data that I work with is in Excel, but sourced from Access; it needs to be sourced though from Excel as this raw data is used for other reports as well.


How can I create, using the attached raw data, a PIVOT that gives me ALL data for an individual worker? Please note that Column A (Worker) does not exist, but I THINK I need to have a column created in the raw data that identifies all of the individual workers that can then be used to summarize the data.


I pasted a sample spreadsheet of the raw data and manually created the below (hopefully no errors) that should align of what I NEED to create below that. I ultimately need a pivot, from the raw sample, that is refreshable with the below view.


Within the sample, here is some additional information that needs to be taken into account:
- The CS (Client Support) column can currently be used with the Order Status to create a pivot view that is useful, but I need to also summarize the primary/secondary views.
- All orders (Column A - Order Number) are unique.
- The CS (Column H) has Orders Status (Column B) which is displayed below. (In Process, For Review and Available).
- The CS could also be a primary (Column I) or Secondary (Column J).
- The 1st total column below is the number of orders in the columns to the left (Bob James has 5 (3+1+1).
- An order (Column B) can have more than one job (Column G). So, the 2nd total should be ALL unique jobs (not orders) that the primary and secondary are working on. The 1st total is based on orders (Column K is Unique_IND which is the unique orders so the example below with 2 jobs only counts as 1 order.
Example: Order 47223 (Column A) is one order with 2 jobs (E999 and E243). For the primary or secondary, that counts as 2 jobs.


The big issue is that I THINK I need a new column (Worker in the below) that is unique of all of the CS, Priimary and Secondary workers that can then be used to pivot. Hopefully that make sense because I can't see how I can summarize (in a pivot) the CS and the Primary/Secondary.

Sample of the raw data:

[TABLE="width: 1219"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Order Number[/TD]
[TD]Order Status[/TD]
[TD]Company Name[/TD]
[TD]Order Name[/TD]
[TD]Setup Description[/TD]
[TD]Complexity[/TD]
[TD]Job Number[/TD]
[TD]CS[/TD]
[TD]Primary[/TD]
[TD]Secondary[/TD]
[TD]Unique_Ind[/TD]
[/TR]
[TR]
[TD]45764[/TD]
[TD]In Process[/TD]
[TD]Company A[/TD]
[TD]Program A[/TD]
[TD]Modified[/TD]
[TD]Complex[/TD]
[TD]A123[/TD]
[TD]Smith[/TD]
[TD]Smith[/TD]
[TD]Johnson[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]45892[/TD]
[TD]In Process[/TD]
[TD]Company B[/TD]
[TD]Program B[/TD]
[TD]As-Is (No Changes)[/TD]
[TD]Average[/TD]
[TD]B222[/TD]
[TD]Smith[/TD]
[TD]Smith[/TD]
[TD]Jones[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]46601[/TD]
[TD]For Review[/TD]
[TD]Company C[/TD]
[TD]Program C[/TD]
[TD]Modified[/TD]
[TD]Complex[/TD]
[TD]C343[/TD]
[TD]Jones[/TD]
[TD]Williams[/TD]
[TD]Jones[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]46634[/TD]
[TD]In Process[/TD]
[TD]Company D[/TD]
[TD]Program D[/TD]
[TD]As-Is (No Changes)[/TD]
[TD]Average[/TD]
[TD]D323[/TD]
[TD]Jones[/TD]
[TD]Davis[/TD]
[TD]Hernandez[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]46784[/TD]
[TD]On Hold - Customer[/TD]
[TD]Company E[/TD]
[TD]Program E[/TD]
[TD]New[/TD]
[TD]Complex[/TD]
[TD]F321[/TD]
[TD]Anderson[/TD]
[TD]Taylor[/TD]
[TD]Hernandez[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]47088[/TD]
[TD]For Review[/TD]
[TD]Company F[/TD]
[TD]Program F[/TD]
[TD]New[/TD]
[TD]Simple[/TD]
[TD]E332[/TD]
[TD]Anderson[/TD]
[TD]Johnson[/TD]
[TD]Taylor[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]47096[/TD]
[TD]Available[/TD]
[TD]Company G[/TD]
[TD]Program G[/TD]
[TD]New[/TD]
[TD]Simple[/TD]
[TD]G232[/TD]
[TD]Smith[/TD]
[TD]Johnson[/TD]
[TD]Taylor[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]47223[/TD]
[TD]Available[/TD]
[TD]Company H[/TD]
[TD]Program H1[/TD]
[TD]Modified[/TD]
[TD]Average[/TD]
[TD]E999[/TD]
[TD]Cruz[/TD]
[TD]Hernandez[/TD]
[TD]Davis[/TD]
[TD]Unique[/TD]
[/TR]
[TR]
[TD]47223[/TD]
[TD]In Process[/TD]
[TD]Company H[/TD]
[TD]Program H2[/TD]
[TD]Modified[/TD]
[TD]Average[/TD]
[TD]E243[/TD]
[TD]Cruz[/TD]
[TD]Hernandez[/TD]
[TD]Davis[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1219"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Her's what I need to create as a PIVOT in a different tab.


Order Status - CS: Primary / Secondary Work:
Worker: In Process For Review Available On Hold Total:CS Primary: Secondary: Total:
Smith 2 0 1 0 3 2 0 2
Jones 1 1 0 0 2 0 2 2
Anderson 0 1 0 1 2 0 0 0
Cruz 1 0 1 0 2 0 0 0
Williams 0 0 0 0 0 1 0 1
Davis 0 0 0 0 0 0 2 2
Taylor 0 0 0 0 0 1 2 3
Johnson 0 0 0 0 0 2 1 3
Hernandez 0 0 0 0 0 2 2 4

 

[mrmmickle1]

The big issue is that I THINK I need a new column (Worker in the below) that is unique of all of the CS, Priimary and Secondary

Maybe you could just concatenate the three together and then use that as the unique ID...