All, I am having a problem working out how I can combine different columns of data into one column to allow for an aggregation sum and ultimately creation of a bar chart with the counts per analyst.
My data is structured as below:
For each proposal ID there could be up to 4 people working and each are required to report 'buckets' of time spent.
| Proposal ID | Lead Sponsor | Lead Analyst | Additional Analyst 1 | Additional Analyst 2 | Time Spent Sponsor | Time Spent Lead | Time Spent Analyst 1 | Time Spent Analyst 2 |
| PP1 | Dave | Mike | Jane | Frazer | 5-10 Hrs | 1-5 Hrs | 1-5 Hrs | 1-5 Hrs |
| PP2 | Mike | Jane | 1-5 Hrs | 5-10Hrs |
| PP3 | Steve | Dave | Jane | Sarah | 5-10 Hrs | 10+ Hrs | 1-5 Hrs | 1-5 Hrs |
| PP4 | Sarah | Helen | Dave | 5-10 Hrs | 1-5 Hrs | 1-5 Hrs |
| PP5 | Jennie | 1-5 Hrs |
What I want to be able to do is pick an individual staff member and see how many of each time bucket they have reported. As the names and the respective time buckets are split across different columns of data, I cannot see how to combine.
To further complicate this, the table you see posted above (and attached) is actually already a concatenated table from 4 different regional files to give one long list of global proposals and global staff members.
Any guidance on this would be much appreciated.
Thanks,
Ben
