pull data from a table and sort into list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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.
 
PL said:
It works!!! Thanks Max.
Click to expand...

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.
Click to expand...

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.

---
 
Back
Top