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

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.

Answer 3

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 4

A slightly different approach here, in three parts:

• Data entry table -- Each session, someone would enter the role players and the roles. Table is sorted by date (descending).
• Cross-Reference Table -- Each name cross-referenced to the most recent date they served in each role
• Next up list

It is very low maintenance. Once it is set up, the only work is entering the three names each day and extending the date range as required.

Data Entry Table

Here is the table (A1:D10). Note: No formulas here. Each week, enter three names based on the next-up list below.

Date    Role1   Role2   Role3  
21-Jan            
20-Jan            
19-Jan  Alice   Chuck   Bruce  
18-Jan  Chuck   Bruce   Derek  
17-Jan  Chuck   Derek   Alice  
16-Jan  Emily   Bruce   Chuck  
15-Jan  Alice   Chuck   Emily  
14-Jan  Derek   Emily   Alice  
13-Jan  Alice   Bruce   Emily  

Cross-Reference Table

The cross-reference table (A14:D19) below will display the most recent day that each name performed each role.

=IF(ISNA(MATCH($A15,B$2:B$10,0)),"",INDEX($A$2:$A$10,MATCH($A15,B$2:B$10,0)))

Name    Role1   Role2   Role3   Role4  
Alice   19-Jan          17-Jan  18-Jan  
Bruce           18-Jan  19-Jan  14-Jan  
Chuck   18-Jan  19-Jan  16-Jan  13-Jan  
Derek   14-Jan  17-Jan  18-Jan  19-Jan  
Emily   16-Jan  14-Jan  15-Jan  17-Jan  

If all names have performed the role at least once, it will then provide the most recent date that matches. Blank cell if the name has never performed the role

Next Up List

The Next Up section (A12:D12) looks for the earliest date in each role's column and provides the matching name. It works by finding the minimum number in the column and then returning its row. Empty cells (never performed) are prioritized.

=IF(ISNA(MATCH("",B$15:B$19,0)),INDEX($A$15:$A$19,MATCH(MIN(B$15:B$19),B$15:B$19,0)),INDEX($A$15:$A$19,MATCH("",B$15:B$19,0)))

Next-Up Role1   Role2   Role3  
Name    Bruce   Alice   Emily  

^{All formulas can be dragged down and right. If you wish to extend the date range, insert additional rows below row 2 and the formulas will continue to work. For example, to extend to 31-Jan, select rows 3:12 and insert, then cells A13:A14 and drag the handle up to cell A2.}