Here's one way using formulas to achieve what you're after ..

Assume the source table is in a sheet: X, within A1:CC30 as per post

Define* 2 named ranges: Dates & Times, whe
Dates =X!$B$1:$CC$1
Times =X!$A$2:$A$30
*via Insert Name Define

Then in a new sheet,
with the labels in A1:C1 :Date, Time, SubjectID

In A2:
=INDEX(Dates,INT((ROWS($1:1)-1)/29)+1)

In B2:
=INDEX(Times,MOD(ROWS($1:1)-1,29)+1)

Note: "29" is used in the above 2 formulas
as the source table in X holds a total of 29 rows (ie A2:A30)

In C2:
=INDEX(X!B$2:CC$30,MATCH(B2,Times,0),MATCH(A2,Date s,0))

Select A2:C2, copy down by 2320** rows to C2321. Cols A to C will extract
the contents of the entire source table from X in the desired manner. Kill
all formulas in cols A to C with an "in-place" copypaste special as values.
Then filter col C for zeros (zeros will be returned where there's no subject
ids listed within X) and simply select & delete away these filtered rows. The
resulting cols A to C will be exactly what you're after.

**as the source table in X comprises 80 cols x 29 rows
= 80 x 29 = 2320 content cells
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"PL" wrote:
I have a timetable where with the date show at row 1 (from B1 to CC1) and
time at column A (from A2 to A30). The rest of the data are the subjectID
(about 20 subjects) which fall on different day and time.

I would like to pull out the data and sort into a list format whereby Date
at Column A, SubjectID at Column C and Time at Column B in new spreadsheet.
Those dates with no lesson/no subjectID will not be captured.

Can the excel functions do that? Or how to write a macro on this? Please
help.