Creating a Graphical Schedule out of Table Data

R

rswhalen

New Member
Joined
Feb 17, 2016
Messages
1
I am attempting to take a schedule in table format

e.g.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]CLASSROOM A[/TD]
[TD]5/1/17[/TD]
[TD]8:00 AM[/TD]
[TD]12:00 PM[/TD]
[TD]BOB[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CLASSROOM B[/TD]
[TD]5/1/17[/TD]
[TD]10:00 AM[/TD]
[TD]11:00 AM[/TD]
[TD]JANE[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CLASSROOM A[/TD]
[TD]5/2/17[/TD]
[TD]9:00 AM[/TD]
[TD]10:00 AM [/TD]
[TD]CARL[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]CLASSROOM B[/TD]
[TD]5/2/17[/TD]
[TD]9:00 AM[/TD]
[TD]11:00 AM[/TD]
[TD]BOB[/TD]
[/TR]
</tbody>[/TABLE]

and convert it into a more graphical format

e.g.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]5/1/2017[/TD]
[TD]5/2/2017[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CLASSROOM A[/TD]
[TD]8:00 AM[/TD]
[TD]BOB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CLASSROOM A[/TD]
[TD]9:00 AM[/TD]
[TD]BOB[/TD]
[TD]CARL[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]CLASSROOM A[/TD]
[TD]10:00 AM[/TD]
[TD]BOB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]CLASSROOM A[/TD]
[TD]11:00 AM[/TD]
[TD]BOB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]CLASSROOM A[/TD]
[TD]12:00 PM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]CLASSROOM B[/TD]
[TD]8:00 AM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]CLASSROOM B[/TD]
[TD]9:00 AM
[/TD]
[TD][/TD]
[TD]BOB[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]CLASSROOM B[/TD]
[TD]10:00 AM[/TD]
[TD]JANE[/TD]
[TD]BOB[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]CLASSROOM B[/TD]
[TD]11:00 AM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]CLASSROOM B[/TD]
[TD]12:00 PM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The closest I was able to get was by finding start and stop times through and INDEX array similar to that below:

{=INDEX(Sheet1!E1:E4, IFERROR(MATCH(A2&B2&C1, Sheet1!A1:A4&Sheet1!C1:C4&Sheet1!B1:B4, 0),MATCH(A2&B2&C1, Sheet1!A1:A4&Sheet1!D1:D4&Sheet1!B1:B4, 0))}


Any help would be greatly appreciated! Thanks!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
rswhalen said:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]5/1/2017
[/TD]
[TD]5/2/2017
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]CLASSROOM A
[/TD]
[TD]8:00 AM
[/TD]
[TD]BOB
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]CLASSROOM A
[/TD]
[TD]9:00 AM
[/TD]
[TD]BOB
[/TD]
[TD]CARL
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]CLASSROOM A
[/TD]
[TD]10:00 AM
[/TD]
[TD]BOB
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]CLASSROOM A
[/TD]
[TD]11:00 AM
[/TD]
[TD]BOB
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]CLASSROOM A
[/TD]
[TD]12:00 PM
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]CLASSROOM B
[/TD]
[TD]8:00 AM
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]CLASSROOM B
[/TD]
[TD]9:00 AM
[/TD]
[TD][/TD]
[TD]BOB
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]CLASSROOM B
[/TD]
[TD]10:00 AM
[/TD]
[TD]JANE
[/TD]
[TD]BOB
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]CLASSROOM B
[/TD]
[TD]11:00 AM
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]CLASSROOM B
[/TD]
[TD]12:00 PM
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

rswhalen,
Welcome to the Forum.
While this is not exactly the results you were looking for, here is an example of results Using a Pivot Table:

Excel 2007
G
H
I
J
K
Count of Name
Date
Classroom
Start
End
CLASSROOM A
CLASSROOM B

<tbody>
[TD="align: center"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]

[TD="align: right"]5/1/2017
[/TD]
[TD="align: right"]5/2/2017
[/TD]

[TD="align: center"]5
[/TD]

[TD="align: right"]8:00 AM
[/TD]
[TD="align: right"]12:00 PM
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9:00 AM
[/TD]
[TD="align: right"]10:00 AM
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]7
[/TD]

[TD="align: right"]9:00 AM
[/TD]
[TD="align: right"]11:00 AM
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10:00 AM
[/TD]
[TD="align: right"]11:00 AM
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



To do this...Select your entire table including headings (I added headings: Classroom, Date, Start, End, and Name). Then select 'Insert' , then 'Pivot Table'. You must select where you want the pivot table displayed. I chose the same sheet in cells 'G2:M10'. The table can be modified after it has been set up.

This is how I set up the selections:
With the 'Pivot Table Field List ' window that comes up automatically when you insert a pivot table, set 'Date' as a Column Label, and 'Classroom', 'Start', and 'End' as Row labels, in that order.

In the pivot table there were some 'Subtotals' and 'Grand Totals' which I removed using right click and remove. This is pretty fast and easy to set up. I hope this is helpful.
Perpa
 
Upvote 0
Perpa said:
rswhalen,
Welcome to the Forum.
While this is not exactly the results you were looking for, here is an example of results Using a Pivot Table:

Excel 2007
G
H
I
J
K
Count of Name
Date
Classroom
Start
End
CLASSROOM A
CLASSROOM B

<tbody>
[TD="align: center"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]

[TD="align: right"]5/1/2017
[/TD]
[TD="align: right"]5/2/2017
[/TD]

[TD="align: center"]5
[/TD]

[TD="align: right"]8:00 AM
[/TD]
[TD="align: right"]12:00 PM
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9:00 AM
[/TD]
[TD="align: right"]10:00 AM
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]7
[/TD]

[TD="align: right"]9:00 AM
[/TD]
[TD="align: right"]11:00 AM
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10:00 AM
[/TD]
[TD="align: right"]11:00 AM
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



To do this...Select your entire table including headings (I added headings: Classroom, Date, Start, End, and Name). Then select 'Insert' , then 'Pivot Table'. You must select where you want the pivot table displayed. I chose the same sheet in cells 'G2:M10'. The table can be modified after it has been set up.

This is how I set up the selections:
With the 'Pivot Table Field List ' window that comes up automatically when you insert a pivot table, set 'Date' as a Column Label, and 'Classroom', 'Start', and 'End' as Row labels, in that order.

In the pivot table there were some 'Subtotals' and 'Grand Totals' which I removed using right click and remove. This is pretty fast and easy to set up. I hope this is helpful.
Perpa
Click to expand...

rswhalen,
Just noticed I forgot to add the 'Name' as a Row Label after 'End'. Here is the revised Pivot Table:

Excel 2007
GHIJKL
3Count of NameDate
4ClassroomStartEndName5/1/20175/2/2017
5CLASSROOM A8:00 AM12:00 PMBOB1
69:00 AM10:00 AMCARL1
7CLASSROOM B9:00 AM11:00 AMBOB1
810:00 AM11:00 AMJANE1
Sheet1


To get the '1's to the right hand side just drag 'Name' to the window 'Sum Values'.
Sorry if any confusion.
Perpa
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
68
cooleomter
C
M
  • Solved
sumifs using date and time values
Replies
9
Views
327
Asbestos_Jen
Asbestos_Jen
A
  • Question Question
Hi. I'm trying to fill 1 for the time between arrival and departure.
Replies
1
Views
164
Asbestos_Jen
Asbestos_Jen
T
  • Question Question
VBA help to count Daytime,Nighttime and Summary
2 3
Replies
20
Views
840
Alex Blakenburg
A
B
  • Solved
Possible to pull apart 1st section of string for date & time
2 3
Replies
23
Views
450
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

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