Help with Room Utilization Grid

D

debmullin

New Member
Joined
May 3, 2019
Messages
3
Hi - I have data that represents classes assigned to a room. Some classes are two days a week, some classes are 1 day a week. I would like to have a visual of the room utilization and open slots. I tried a pivot table with class start and stop time as a row and Class Day as a column. That doesn't seem to do it. Anyone have an idea for a grid. Here is a sample of one of the rooms with fake instructor names:

Start TimeEnd TimeClass DayCATALOG #CLASS SECTIONRoomInstructor
10:30 AM11:45 AMM210BBC121Kenya Kim
12:00 PM1:15 PMM213BBC121Kenya Kim
3:00 PM4:15 PMM221BBC121Kenya Kim
4:30 PM5:45 PMM224BBC121Kenya Kim
1:30 PM2:45 PMM 1A49BBC121Raleigh Roth
9:00 AM10:15 AMM 1A69BBC121Raleigh Roth
9:00 AM10:15 AMT 1AF06BBC121Abe Hampton
10:30 AM11:45 AMT 1A26BBC121Claudia Holmes
12:00 PM1:15 PMT 1A42BBC121Claudia Holmes
10:30 AM11:45 AMW210BBC121Kenya Kim
12:00 PM1:15 PMW213BBC121Kenya Kim
3:00 PM4:15 PMW221BBC121Kenya Kim
4:30 PM5:45 PMW224BBC121Kenya Kim
1:30 PM2:45 PMW 1A49BBC121Raleigh Roth
9:00 AM10:15 AMW 1A69BBC121Raleigh Roth
9:00 AM10:15 AMR 1AF06BBC121Abe Hampton
10:30 AM11:45 AMR 1A26BBC121Claudia Holmes
12:00 PM1:15 PMR 1A42BBC121Claudia Holmes
9:30 AM12:15 PMF 1A78BBC121Ana Romero

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

And this is what I want it to look like:

M
T
W
R
F
9:00 AM-10:15AM
Roth 1A-69
Hampton 1AF-06
Roth 1A-69
Hampton 1AF-06
Holmes 1A-78 9:30 - 12:15
10:30 AM - 11:45 AM
Kim 2-13
Holmes 1A-26
Kim 2-13
Holmes 1A-26
12:00 PM - 1:15PM
Kim 12-1:15
Holmes 1A-42
Kim 12-1:15
Holmes 1A-42
1:30 PM - 2:45 PM
Roth 1A-49
Roth 1A-49

3:00 PM - 4:15PM
Kim 2-21
Kim 2-21

4:30 PM - 5:45 PM
Kim 2-24
Kim 2-24


<tbody>
</tbody>




Thanks for any advice!

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
is that what you want ?

SourceResult
Start TimeEnd TimeClass DayCATALOG #CLASS SECTIONRoomInstructorTimeMTWRF
10:30 AM​
11:45 AM​
M
2​
10​
BBC121Kenya Kim09:00 - 10:15Roth 1A-69Hampton 1AF-6Roth 1A-69Hampton 1AF-6
12:00 PM​
1:15 PM​
M
2​
13​
BBC121Kenya Kim09:30 - 12:15Romero 1A-78
3:00 PM​
4:15 PM​
M
2​
21​
BBC121Kenya Kim10:30 - 11:45Kim 2-10Holmes 1A-26Kim 2-10Holmes 1A-26
4:30 PM​
5:45 PM​
M
2​
24​
BBC121Kenya Kim12:00 - 13:15Kim 2-13Holmes 1A-42Kim 2-13Holmes 1A-42
1:30 PM​
2:45 PM​
M1A
49​
BBC121Raleigh Roth13:30 - 14:45Roth 1A-49Roth 1A-49
9:00 AM​
10:15 AM​
M1A
69​
BBC121Raleigh Roth15:00 - 16:15Kim 2-21Kim 2-21
9:00 AM​
10:15 AM​
T1AF
6​
BBC121Abe Hampton16:30 - 17:45Kim 2-24Kim 2-24
10:30 AM​
11:45 AM​
T1A
26​
BBC121Claudia Holmes
12:00 PM​
1:15 PM​
T1A
42​
BBC121Claudia Holmes
10:30 AM​
11:45 AM​
W
2​
10​
BBC121Kenya Kim
12:00 PM​
1:15 PM​
W
2​
13​
BBC121Kenya Kim
3:00 PM​
4:15 PM​
W
2​
21​
BBC121Kenya Kim
4:30 PM​
5:45 PM​
W
2​
24​
BBC121Kenya Kim
1:30 PM​
2:45 PM​
W1A
49​
BBC121Raleigh Roth
9:00 AM​
10:15 AM​
W1A
69​
BBC121Raleigh Roth
9:00 AM​
10:15 AM​
R1AF
6​
BBC121Abe Hampton
10:30 AM​
11:45 AM​
R1A
26​
BBC121Claudia Holmes
12:00 PM​
1:15 PM​
R1A
42​
BBC121Claudia Holmes
9:30 AM​
12:15 PM​
F1A
78​
BBC121Ana Romero
 
Upvote 0
with M-code (PowerQuery)

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Start Time", type time}, {"End Time", type time}, {"Class Day", type text}, {"CATALOG #", type any}, {"CLASS SECTION", Int64.Type}, {"Room", type text}, {"Instructor", type text}}),
    MergeCatClass = Table.CombineColumns(Table.TransformColumnTypes(Type, {{"CATALOG #", type text}, {"CLASS SECTION", type text}}, "en-GB"),{"CATALOG #", "CLASS SECTION"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    Split = Table.SplitColumn(MergeCatClass, "Instructor", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Instructor.1", "Instructor.2"}),
    MergeInstrCatClass = Table.CombineColumns(Split,{"Instructor.2", "Merged"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged.1"),
    Pivot = Table.Pivot(MergeInstrCatClass, List.Distinct(MergeInstrCatClass[#"Class Day"]), "Class Day", "Merged.1"),
    MergeTime = Table.CombineColumns(Table.TransformColumnTypes(Pivot, {{"Start Time", type text}, {"End Time", type text}}, "en-GB"),{"Start Time", "End Time"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Time"),
    GrpTime = Table.Group(MergeTime, {"Time"}, {{"Count", each _, type table}}),
    ExtractM = Table.TransformColumns(Table.AddColumn(GrpTime, "M", each List.Distinct(Table.Column([Count],"M"))), {"M", each Text.Combine(List.Transform(_, Text.From)), type text}),
    ExtractT = Table.TransformColumns(Table.AddColumn(ExtractM, "T", each List.Distinct(Table.Column([Count],"T"))), {"T", each Text.Combine(List.Transform(_, Text.From)), type text}),
    ExtractW = Table.TransformColumns(Table.AddColumn(ExtractT, "W", each List.Distinct(Table.Column([Count],"W"))), {"W", each Text.Combine(List.Transform(_, Text.From)), type text}),
    ExtractR = Table.TransformColumns(Table.AddColumn(ExtractW, "R", each List.Distinct(Table.Column([Count],"R"))), {"R", each Text.Combine(List.Transform(_, Text.From)), type text}),
    ExtractF = Table.TransformColumns(Table.AddColumn(ExtractR, "F", each List.Distinct(Table.Column([Count],"F"))), {"F", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    ExtractF[/SIZE]
 
Upvote 0
Another Power Query solution. Different approach, same results.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Time", type time}, {"End Time", type time}, {"Class Day", type text}, {"CATALOG #", type text}, {"CLASS SECTION", type text}, {"Room", type text}, {"Instructor", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Instructor", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Instructor.1", "Instructor.2"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each [Instructor.2] & " " & [#"CATALOG #"] & "-" & [CLASS SECTION]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"CATALOG #", "CLASS SECTION", "Room", "Instructor.1", "Instructor.2"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Class Day"]), "Class Day", "Custom"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,"",Replacer.ReplaceValue,{"M", "T", "W", "R", "F"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value", {{"Start Time", type text}, {"End Time", type text}}, "en-US"),{"Start Time", "End Time"},Combiner.CombineTextByDelimiter(" to ", QuoteStyle.None),"Time")
in
    #"Merged Columns"
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Condition calculation
Replies
1
Views
139
shknbk2
S
R
  • Solved
vlookup, INDEX, or SUMPRODUCT???
2
Replies
10
Views
331
KRice
K
Q
  • Solved
Calculating total number of hours within a period, counting only specific days of the week, and specific time intervals.
Replies
8
Views
630
Dave Patton
D
HappyChappy
  • Question
Bracket Error
Replies
3
Views
128
Peter_SSs
Peter_SSs
T
  • Question
Time Sheet question
2
Replies
15
Views
508
tfowlerusa
T

Forum statistics

Threads
1,214,875
Messages
6,122,047
Members
449,064
Latest member
scottdog129

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top