Create a list of courses with a month filter

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Create a list of courses with a month filter

  1. #1
    New Member
    Join Date
    Oct 2011
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Create a list of courses with a month filter

     
    Hello All

    I have a long list of course data from which i want to create a list of the courses that were available against a month that i pick. In turn this will enable me to create a graph against the month.


    This is how the data looks at present (This is not the complete list!)
    Date of course Name Of Course Delegate Department Planned number of attendees Actual Number of Attendees
    02/04/2012 Manual Handling FC 1 1
    02/04/2012 Manual Handling CS 2 2
    02/04/2012 Manual Handling WC 3 3
    02/04/2012 Manual Handling Engineering 4 2
    02/04/2012 Manual Handling Process Tech 5 5
    02/04/2012 Manual Handling Quality 5 1
    02/04/2012 Manual Handling Customer Services 1
    02/04/2012 Manual Handling CI 4 4
    02/04/2012 Manual Handling HR 3 1
    01/05/2012 Leadership - managing performance problems FC 2 1
    01/05/2012 Leadership - managing performance problems CS 2 2
    01/05/2012 Leadership - managing performance problems WC 3 3
    01/05/2012 Leadership - managing performance problems Engineering 4 2
    01/05/2012 Leadership - managing performance problems Process Tech 5 5
    01/05/2012 Leadership - managing performance problems Quality 5 1
    01/05/2012 Leadership - managing performance problems Customer Services 1
    01/05/2012 Leadership - managing performance problems CI 4 4
    01/05/2012 Leadership - managing performance problems HR 1 1
    01/05/2012 Fire Extinguisher Training Admin 1 0
    02/05/2012 Discipline & Grievance FC 4 3
    02/05/2012 Discipline & Grievance CS 9 8
    02/05/2012 Discipline & Grievance WC 5 4
    02/05/2012 Discipline & Grievance Engineering 3 3
    02/05/2012 Discipline & Grievance Process Tech 1
    02/05/2012 Discipline & Grievance Labs 1 1


    So what i want to do is say that the month is April (01/04/2012 to 30/04/2012) & create a list of courses that were available during that date range. I have tried Pivot tables but they aren't really doing what i want them to do! - though i have to say I am a TOTAL novice at pivot tables!

    Any suggestions will be gratefully recieved

    Petethecat

  2. #2
    Board Regular
    Join Date
    Nov 2011
    Location
    San Jose, CA
    Posts
    2,277
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create a list of courses with a month filter

    A pivot table would work if you group the dates by month in the pivot table and then filter only for, say April.

    An easier way is to turn on Autofilters (Select all the cells in your database, then click Data-Filter) and filter the dates for just April. In case you're not familiar with filters this link may be helpful: Excel AutoFilter or Advanced Filter? | Contextures Blog

  3. #3
    New Member
    Join Date
    Oct 2011
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create a list of courses with a month filter

    Quote Originally Posted by RonB1111 View Post
    A pivot table would work if you group the dates by month in the pivot table and then filter only for, say April.

    An easier way is to turn on Autofilters (Select all the cells in your database, then click Data-Filter) and filter the dates for just April. In case you're not familiar with filters this link may be helpful: Excel AutoFilter or Advanced Filter? | Contextures Blog
    Yes a filter would create the concise list against the month - however it would be in the wrong place. I am creating a staging area using sumifs to extract the data i want. One of the criteria is to filter the course (Column B) with the identifying criteria being the course names. This is easy to do i have a seperate list of all the courses are selected via a pulldown menu - which keeps spelling errors out thus meaning the formula works OK.


    So if it is possible I want to use the staging area to create the list - a bit like this:

    Training Attendance By Course for: FastCat CSF WashCoat Engineering
    May-12 Planned Actual Planned Actual Planned Actual Planned Actual
    Manual Handling 0 0 0 0 0 0 0 0
    Leadership - managing performance problems 2 1 2 2 3 3 4 2
    Discipline & Grievance 4 3 9 8 5 4 3 3
    Leadership - setting performance expectations 1 1 4 4 0 0 1 1
    Fire Extinguisher Training 1 1 0 0 2 1 1 1
    Mats Training 1 1 1 1 1 1 0 0


    So the numbers are the sumifs formula - which work OK & the "MAY-12" bit is where i pick the month. So underneath the month is where the courses for that month are automatically listed.

    By doing it this way i can create a graph from the data that shows the YTD attendences (i already have this working perfectly) & then a seperate graph that shows the Months figures (the month of my choosing)

    I guess what i want to do is create a list of course done for the month but i am at a loss as to how i extract that bit of data. I have looked at Pivot tables (actually kinda worked - just in the wrong area!) VLOOKUPS - I even looked at INDEX but non seem ideally suited for the task in hand.

    I really appreciate your help so far - if you have anything more that you can add that would be a life saver!!!

  4. #4
    Board Regular
    Join Date
    Nov 2011
    Location
    San Jose, CA
    Posts
    2,277
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create a list of courses with a month filter

    I may not be following correctly, but it sounds like you have everything for the table in your last post except the names of the courses that were held in the selected month. So for May you want Manual Handling to not be listed because 0 people attended but you want the other courses listed that had attendees - is that right or am I missing something?

  5. #5
    New Member
    Join Date
    Oct 2011
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Create a list of courses with a month filter

      
    Quote Originally Posted by RonB1111 View Post
    I may not be following correctly, but it sounds like you have everything for the table in your last post except the names of the courses that were held in the selected month. So for May you want Manual Handling to not be listed because 0 people attended but you want the other courses listed that had attendees - is that right or am I missing something?

    That is exactly right

    Like i said i kind of have a Pivot table working which will enable me to filter for one month - with a bit of faffing about!

    It would be really great to be able to select two months - lets say April & May & see what courses we held during that time & the attendance figures. Trouble is that when you choose two months it changes the description in the pivot table box to "selection" which stops the sumifs formula from working.

    That is where i am stuck & would like to hear any ideas

    Cheersloads
    Petethecat

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com