Code Help

[Jcraig713]

Hello. I need to know how to write some code that will identify rooms that
are scheduled to be used at the same time. See my source data below:

ColA ColB ColC ColD ColE ColF ColG
RMP Period Term Code Sec course Tchr
A4 1 HS1 HSCT100 1 Basic Bus Apps Adolfs, Patricia
A4 1 HS2 HSCT100 6 Basic Bus Apps Adolfs, Patricia
Gym 1 HS1 HSPE200 2 Team Sports I Deutsch, Steven
B10 1 HS2 HSSS211 10 Civics Clifton, Philip
B12 1 HS1 HSSS110 2 US History S1 Thompson, Kourtney
B12 1 HS1 HSSS316 1 Modern World Unknown 3
B12 1 HS2 HSSS111 2 US History S2 Thompson, Kourtney
B12 1 HS2 HSSS316 6 Modern World Unknown 3

So I have about 3000 records. See room B12 above, this is a double booked
room as in both Semester 1 and 2 Period one has two classes sceduled to meet
in B12. I would like to be able to run code that would strip out all the
duplicate rooms from the non-duplicate rooms based on Semester and Period;
each room can only be scheduled once per term. Any assistance is "way"
appreciated.

 

[Bernie Deitrick]

J.,

To identify double-booked rooms, use a formula in column H

=SUMPRODUCT(($A$2:$A$3000=A2)*($B$2:$B$3000=B2)*($C$2:$C$3000=C2))

Adjust the 3000 to your actual number of rows....Then copy down.

That will count the number of bookings per room, period, and term.

Then sort your table, first descending on column H, and second on column A and third on column B.

HTH,
Bernie
MS Excel MVP

 

[Jcraig713]

Thanks!

J.,

To identify double-booked rooms, use a formula in column H

=SUMPRODUCT(($A$2:$A$3000=A2)*($B$2:$B$3000=B2)*($C$2:$C$3000=C2))

Adjust the 3000 to your actual number of rows....Then copy down.

That will count the number of bookings per room, period, and term.

Then sort your table, first descending on column H, and second on column A and third on column B.

HTH,
Bernie
MS Excel MVP