2 Replies Latest reply: Sep 1, 2014 7:57 AM by Ben Roberts RSS

    Combining Text Columns with Count

    Ben Roberts

      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 IDLead SponsorLead AnalystAdditional Analyst 1Additional Analyst 2Time Spent SponsorTime Spent LeadTime Spent Analyst 1Time Spent Analyst 2
      PP1DaveMikeJaneFrazer5-10 Hrs1-5 Hrs1-5 Hrs1-5 Hrs
      PP2MikeJane1-5 Hrs5-10Hrs
      PP3SteveDaveJaneSarah5-10 Hrs10+ Hrs1-5 Hrs1-5 Hrs
      PP4SarahHelenDave5-10 Hrs1-5 Hrs1-5 Hrs
      PP5Jennie1-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.