CHAR DATA IN EXCEL PIVOT TABLE DATA SECTION? 1

[bkj123]

Hello - The list below is a class room schedule for the morning session of an elemtary school. It lists the start and end times of each period as well as the class room and subject being taught. I'd like to get the data into a grid like that shown below the list. I thought a pivot table would work, but I did not know how to put character data (i.e. subject) in the data section. What is the best way to accomplish this?

Thank you!

START END ROOM SUBJECT
8 9 ROOM1 MATH101
9 10 ROOM1 MATH201
10 11 ROOM1 WRITING101
11 12 ROOM1 READING101
8 9 ROOM2 WRITING101
9 10 ROOM2 READING101
10 11 ROOM2 MATH101
11 12 ROOM2 MATH201
8 9 ROOM3 READING101
9 10 ROOM3 MATH101
10 11 ROOM3 MATH201
11 12 ROOM3 WRITING101


START END ROOM1 ROOM2 ROOM3
8 9 MATH101 WRITING101 READING101
9 10 MATH201 READING101 MATH101
10 11 WRITING101 MATH101 MATH201
11 12 READING101 MATH201 WRITING101

 

[SkipVought]

bkj123,

Add a column, I called Seqn that just sequentially numbers the rows.

I used Insert/Name/Create - Create names in top row
to name my ranges.

Set up the basic structure STARTING IN A1 on a new sheet
[tt]
START END ROOM1 ROOM2 ROOM3
8 9
9 10
10 11
11 12
[/tt]
Use this formula in C2
[tt]
=INDEX(SUBJECT,SUMPRODUCT((ROOM=C$1)*(START=$A2)*Seqn),1)
[/tt]
Copy formula across & down thru the data area.

VOLA!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[bkj123]

Excellent Skip! Thanks for the quick and effective response!