New to Pivot Tables and reporting out data

Copper Contributor

I'm new to Pivot tables and date reporting and have been tasked with creating a visual report. Here is my dilemma:
We have six regional offices. Each Office has a different number of subordinate positions (Interviewers, Probationary Interviewers, Examiners, Probationary Examiners) and Supervisors.  Supervisors are to review the work of the staff on a rotating basis.


I need to create fixed "Groups" containing names of the individuals from the subordinate positions. The number of "groups" is to match the number of Supervisors. (If the office has 8 supervisors, I need to create 8 groups of individuals for that supervisor to review, 12 supervisors, 12 groups, etc)


Here's a sample of the base data I need to use to build the report:



Columns I - W represent Groups 1 - 15 because there are 15 supervisors in this sample office (number of supervisors is input in F1, which then generates entries on Column A)

I've tried a basic vlookup function to extract (what will be) names from Columns B, C, D, E, but it becomes too complex.  Is this a task better accomplished by using a Pivot Table? Or some other means of compiling the data?

I have tried my hand at building a Pivot Table, but the results aren't formatting the way I'd like. 

Bob_H1950_1-1715782135740.png      Bob_H1950_2-1715782805912.png



I'm sure there's an easier way, but, as I said, I'm new to this kind of project. Can anyone help?

1 Reply


You have to unpivot columns B:E.

Use PQ for unpivoting.