Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
pull data from a table and sort into list
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
pull data from a table and sort into list
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
pull data from a table and sort into list
Just to add that in the new sheet,
do format cols A and B as date and time to taste -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
pull data from a table and sort into list
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. "Max" wrote: Just to add that in the new sheet, do format cols A and B as date and time to taste -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
pull data from a table and sort into list
"PL" wrote:
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pull recent pricing data from table | Excel Worksheet Functions | |||
Pull Pivot Table Data | Excel Discussion (Misc queries) | |||
Pull pivot table data | Excel Discussion (Misc queries) | |||
Pull list / dropdown data from another worksheet? | Excel Discussion (Misc queries) | |||
Pull Current Month's Data Out of List - Repost | Excel Discussion (Misc queries) |