VBA script for arriving at current exams

[karthik_kaliappan]

Hello all,

I kind of have a complex report to be built and I am thinking of using a VBA script to arrive at the report. So here is the snap shot of report and it is basically an exam schedule for a day. My team handles (team members name in column ‘O') these exams. Here is what I am trying to achieve:-

1. Trying to figure out the number of concurrent exams running at a given hour. We have exams running round the clock (24 hour setup) and I need to be able figure how many exams are being handled at a given hour. For example – So basically if I look at the number of current exams running at 08:00 AM it is 2 exams, at 09:00 AM it is 4 & at 10:00 AM it is 6 exams. We manually figure this out by looking at the exam start & end time respectively and assuming that all the exams start & end as per the scheduled time.
2. I am also looking at a similar report as above by the associate who is handling the exams.

Column A Column B Column C Column D Column E Column F Column G Column H Column J Column I Column K Column L Column M Column N Column O
CandidateStartTimeIST CandidateEndDateTimeIST Duration TestingStationID ExamType CandidateFirstName CandidateLastName CandidateEmailAddress Local Start IST Exam Start Time Exam End Time Proctor Code To be Handed Over to Shifts Proctor
18-09-2018 06:30 18-09-2018 09:30 3 2057 Exam A Candidate A Candidate A none@email.com 18-09-2018 6:30:00 9:30:00 1 6:00 AM Riya
18-09-2018 07:00 18-09-2018 10:00 3 2072 Exam A Candidate A Candidate A none@email.com 18-09-2018 7:00:00 10:00:00 1 6:00 AM Riya
18-09-2018 08:30 18-09-2018 12:00 3.5 2095 Exam A Candidate A Candidate A none@email.com 18-09-2018 8:30:00 12:00:00 2 8:00 AM Veena
18-09-2018 09:30 18-09-2018 12:30 3 2131 Exam A Candidate A Candidate A none@email.com 18-09-2018 9:30:00 12:30:00 1 6:00 AM Riya
18-09-2018 09:30 18-09-2018 13:00 3.5 2089 Exam A Candidate A Candidate A none@email.com 18-09-2018 9:30:00 13:00:00 2 8:00 AM Veena
18-09-2018 09:30 18-09-2018 12:00 2.5 2020 Exam A Candidate A Candidate A none@email.com 18-09-2018 9:30:00 12:00:00 1 6:00 AM Riya
18-09-2018 10:00 18-09-2018 13:30 3.5 2096 Exam A Candidate A Candidate A none@email.com 18-09-2018 10:00:00 13:30:00 1 6:00 AM Riya
18-09-2018 11:30 18-09-2018 15:30 4 2083 Exam A Candidate A Candidate A none@email.com 18-09-2018 11:30:00 15:30:00 3 10:00 AM Pramila
18-09-2018 11:30 18-09-2018 15:00 3.5 2057 Exam A Candidate A Candidate A none@email.com 18-09-2018 11:30:00 15:00:00 1 6:00 AM Riya
18-09-2018 12:00 18-09-2018 15:30 3.5 2099 Exam A Candidate A Candidate A none@email.com 18-09-2018 12:00:00 15:30:00 3 10:00 AM Pramila
18-09-2018 12:00 18-09-2018 14:30 2.5 2117 Exam A Candidate A Candidate A none@email.com 18-09-2018 12:00:00 14:30:00 2 8:00 AM Veena
18-09-2018 12:30 18-09-2018 16:30 4 2033 Exam A Candidate A Candidate A none@email.com 18-09-2018 12:30:00 16:30:00 2 8:00 AM Veena
18-09-2018 12:30 18-09-2018 16:30 4 2090 Exam A Candidate A Candidate A none@email.com 18-09-2018 12:30:00 16:30:00 2 8:00 AM Veena


Apologies for a messed excel representation. I am not able to upload a screen of the excel.

Thanks,
Karthik

 

[James006]

Hello,

To make your life easier ... why don't you just Insert a Pivot Table ...:wink:

 

[karthik_kaliappan]

Hello,

To make your life easier ... why don't you just Insert a Pivot Table ...:wink:

Hi - Would a pivot table give me the concurrent exams which are running at a given hour?

Thanks,
Karthik

 

[James006]

Hello,

The Pivot table offers a vast array of Grouping features ...:wink:

Your problem will be fixed in less than 10 seconds ... !!!

Have a try ...

 

[karthik_kaliappan]

My apologies....it is not current exams...it is CONCURRENT exams. Sorry about the confusion.

 

[Fluff]

Cross posted https://chandoo.org/forum/threads/vba-script-for-arriving-at-current-exams.40322/

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[karthik_kaliappan]

Cross posted https://chandoo.org/forum/threads/vba-script-for-arriving-at-current-exams.40322/

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Unconditional Apologies for doing so. It is just that a hard deadline pushed me to look at multiple sources. Once again I am sorry.