Determining whether VBA is necessary in Excel, or if a pivot chart/table can suffice

Question

I wish that I had a more precisely descriptive title for this question, but I am not very knowledgeable with Excel spreadsheets or VBA (I'm primarily a Java developer, and don't play much in the Microsoft world).

I'm trying to use an Excel spreadsheet to accomplish something. I need to determine whether I can get there with a PivotTable, PivotChart, or some other built-in functionality... or whether I need to go down the path of writing custom VBA code (or maybe using another platform altogether).

The purpose of the spreadsheet is to help create schedules for an organization (a Toastmasters club). This organization has a roster of members, it meets weekly, and various members are assigned to various roles in a given meeting.

My spreadsheet looks like this:

First Tab

One column... a list of names representing the membership roster.

Second Tab

Each row represents a past meeting date. There are columns for each role, and the cells are to be populated with who served that role on that date. I use Data Validation to have the first tab's roster available inside each cell as a pulldown.

Third Tab (maybe multiple tabs?)

Here's the point of the whole thing. For each of the possible meeting roles, I would like to see which members are the most "overdue" to be assigned for that role. Basically, I want a list of all the club members, sorted in order of how long it's been since they last served that role. People who have never served in that role would be sorted at the top of the list.

Is that third tab data something that can be accomplished with a PivotTable, etc... or am I misunderstanding the purposes and limitations of those tools?

Answer 1

Correct me if I understand wrong. Your data is like that:

First Tab (Sheet):

Name
----
Alice
Bruce
Chuck
Derek
Emily

Second Tab:

Date        Role1    Role2    Role3
--------    -----    -----    -----
13-01-11    Alice    Bruce    Chuck (Each selected from dropdown)
14-01-11    Derek    Emily    Bruce
15-01-11    Alice    Chuck    Derek
16-01-11    Emily    Bruce    Alice
17-01-11    Chuck    Derek    Bruce
18-01-11    Chuck    Bruce    Alice
19-01-11    Alice    Chuck    Bruce

If so PivotTable wouldn't be applicable unless you convert it to the formats that Mike wrote.

Instead, (EDIT: after separating each role column in the same tab and sorting them alphabetically) i.e. make second tab like this (cut-paste-sort):

Date        Role1
--------    -----
13-01-11    Alice
15-01-11    Alice
19-01-11    Alice
17-01-11    Chuck
18-01-11    Chuck
14-01-11    Derek
16-01-11    Emily

Date        Role2
--------    -----
13-01-11    Bruce
16-01-11    Bruce
18-01-11    Bruce
15-01-11    Chuck
19-01-11    Chuck
17-01-11    Derek
14-01-11    Emily etc

Then, you can create tabs for each role (or separate them in the same tab) and use LOOKUP function to see the last time a member has done that role. For example, for Role1, Tab 3 should be like that:

Name     Last Done (Date)
----     -------- 
Alice    19-01-11 [=LOOKUP(A2,Sheet2!$B$2:$B$8,Sheet2!$A$2:$A$8)]
Bruce    N/A
Chuck    18-01-11
Derek    14-01-11
Emily    16-01-11

If you sort from newest to oldest you can see who has never done the role at the top, and who has done the last time at the bottom.

You can also add columns for how many times someone has done a specific role by using COUNT, and how many days/weeks passed since the last time by using TODAY and getting the difference.

Copy-Paste the same functions for other roles (assuming they're not many) at different tabs.

Answer 2

I personally would go with VBA, but I generally lean that way. If you're not a VBA guy, it may be easier to maintain if you do it with formulas and pivot tables. You will have to make some sacrifices, though. So it's worth investigating the non-VBA way to see if the sacrifices are acceptable to you. Here's one way.

On the roster sheet, add columns for all of the roles. In my example, there are two roles: President and Secretary, so Col A is the name, Col B is titled President and Col C is titled Secretary. In B2, put this formula

{=MAX((OFFSET(Meetings!$A$1,1,MATCH(B$1,Meetings!$B$1:$C$1,FALSE),20,1)=$A2)*(OFFSET(Meetings!$A$2,0,0,20,1)))}

There are some assumptions in this formula that I would definitely make more robust, but it's a good proof of concept. You enter this with Control+Shift+Enter because it's an array formula. It will return the maximum date it finds for that name in the President column. Fill that formula down and to the right to suit your data.

Now with the data on the roster sheet, make a pivot table for each "role" column. Put the name in the row area and the role (e.g. President) in the data area. It will probably default to Count of President. Right click on that field and choose Field Settings. Change Summarize By to Max and set the number format to a date.

Next right click on one of the names and choose field settings. Click the Advanced button. Change AutoSort Options to Ascending on Max of President.

The result should be a list of sorted names. The names at the top have gone the longest without being president.

Nothing better than an example. Download one here http://www.dailydoseofexcel.com/excel/Rotary.zip It contains 1 Excel 2003 workbook that shows what I described above.

Answer 3

Yes, a pivot table should suffice, providing you set your data up appropriately in the second tab:

Meeting       Member      Role      Weeks since
1/12/2010     Member 1    Role 1    8 [ =(TODAY()-A2)/7 ]
1/12/2010     Member 2    Role 2    8
1/12/2010     Member 3    Role 3    8
1/12/2010     Member 4    Role 4    8
1/12/2010     Member 5    Role 5    8
8/12/2010     Member 1    Role 1    7
8/12/2010     Member 3    Role 2    7
8/12/2010     Member 7    Role 3    7
8/12/2010     Member 6    Role 4    7
8/12/2010     Member 4    Role 5    7
15/12/2010    Member 1    Role 1    6
15/12/2010    Member 3    Role 2    6
15/12/2010    Member 4    Role 3    6
15/12/2010    Member 2    Role 4    6
15/12/2010    Member 6    Role 5    6
22/12/2010    Member 1    Role 1    5
22/12/2010    Member 2    Role 2    5
22/12/2010    Member 5    Role 3    5
22/12/2010    Member 3    Role 4    5
22/12/2010    Member 4    Role 5    5
29/12/2010    Member 2    Role 1    4
29/12/2010    Member 3    Role 2    4
29/12/2010    Member 4    Role 3    4
29/12/2010    Member 5    Role 4    4
29/12/2010    Member 5    Role 5    4
...

Then you can create your pivot table as follows:

Report filter: none

Column labels: none

Row labels: Role, Member

Values: MIN of Weeks since

Then:

• Remove unnecessary total fields
• Edit the Member field settings and select Layout and Print / Show items with no data. This will display members who have never served a role.
• Change the sort order on Min of Weeks since to Smallest to Largest.

Your pivor table should then look similar to the following:

Role        Member          Min of Weeks since
Role 1      Member 4        
            Member 7        
            Member 3        
            Member 5        
            Member 6        
            Member 2        4
            Member 1        5
Role 2      Member 4        
            Member 7        
            Member 6        
            Member 1        
            Member 5        
            Member 3        4
            Member 2        5
Role 3      Member 6        
            Member 1        
            Member 2        
            Member 4        4
            Member 5        5
            Member 7        7
            Member 3        8
Role 4      Member 7        
            Member 1        
            Member 5        4
            Member 3        5
            Member 2        6
            Member 6        7
            Member 4        8
Role 5      Member 7        
            Member 1        
            Member 3        
            Member 2        
            Member 5        4
            Member 4        5
            Member 6        6

Tested in Excel 2010.