pull data from a table and sort into list

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PL - 25 Jun 2007 12:20 GMT

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.

Reply to this Message

Max - 25 Jun 2007 14:41 GMT

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, where:
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,Dates,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" copy>paste 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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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
[quoted text clipped - 6 lines]
> Can the excel functions do that? Or how to write a macro on this? Please
> help.

Reply to this Message

Max - 25 Jun 2007 14:49 GMT

Just to add that in the new sheet,
do format cols A and B as date and time to taste

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

PL - 25 Jun 2007 20:29 GMT

It works!!! Thanks Max.

The only problem encounter now is data "0" was captured at SubjectID column
on new sheet due to the empty cell at sheet X. Hence, I have to do a filter
to take out all the "0" data.

Overall, it make me save lots of time.

> Just to add that in the new sheet,
> do format cols A and B as date and time to taste

Reply to this Message

Max - 26 Jun 2007 00:20 GMT

> It works!!! Thanks Max.

Glad to hear it worked for you here. .. btw, perhaps you could also provide
feedback to responses given to your earlier other postings.

> The only problem encounter now is data "0" was captured at SubjectID column
> on new sheet due to the empty cell at sheet X. Hence, I have to do a filter
> to take out all the "0" data.

well, if you want it automated all the way through to the final results,
instead of killing the formulas and then filtering for zeros in col
C/deleting manually, you could extend the earlier set-up like this ..

In D2:
=IF(C2=0,"",ROW())

In E2:
=IF(ROWS($1:1)>COUNT($D:$D),"",INDEX(A:A,SMALL($D:$D,ROWS($1:1))))

Copy E2 to G2. Select D2:G2, copy down to G2321 (ie to the last row in cols
A to C). Then you can hide away cols A to D. Cols E to G will auto-return the
final results that you're after, ie the extracts from X w/o the empty lines,
with all result lines neatly bunched at the top.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Rate this thread: