View Full Version : sorting by staff

01-06-2020, 01:13 PM

Bob Phillips
01-07-2020, 06:39 AM

01-07-2020, 11:35 AM
@ RPO1,
Sorry, having a bad day today. I got about half done yesterday.

My style is to break everything into as small bits as possible and to place all code near where it is used. To that end, I have completed all the outputs from the "Paired" sheet and most of the ones from the Status sheet. I still need to test the Status sheet's code.

All that is left is to do the code for the Main module and handle the inputs to the "Paired" sheet.

The functionality of your interim sheet "Sorted" will be handled by the Class Object "PBStatus," AKA Sheets("PBSPstatus") and Standard Module "modMain."

Class Object "Teams," AKA Sheets("Paired"), Has one Read Only Property "GroupsArray" that returns an array of all Client Groups listed in Row 1. It will have a Method that will write a list of Staff under each Client Team.

PBStatus has a read Only Property "ClientCertifiedStaff" that accepts a Client Name and returns a list of Staff qualified by that Client

modMain will read the list of Groups from Teams, break the list into individual groups, then each group into individual Clients. It will then feed each Client to PBStatus.ClientCertifiedStaff and read the returned list of client qualified staff. After the staff of each Client of a group is received, modMain will combine them into a list of fully qualified staff and feed it back to the Teams write Method. This will be repeated for each Group of Clients.

Attached is the work done so far. I work in Excel 2003 so I have built-in help. I will port it to Excel 2013 when done.

01-08-2020, 08:28 PM
PBStatus is done and tested