Excerpt |
---|
The "people" data from Kevin needs to be aggregated, and added to existing faculty info, for it to be useful. |
Kevin provides two spreadsheets whenever you he kindly provides us with a snapshot (monthly?). One is the "grad" spreadsheet (from Pat Hine's data), and the other is a "staff" spreadsheet (from Sharon de Roos's data).
Oliver has a third "faculty" spreadsheet (data from Joyce Boda); not updated except by hand.
Table of Contents |
---|
Replace header row of "grad" spreadsheet.
Grab header from previous master people list.
Copy previous "master" list as a template for the new master list.
Use convention "CCBpeople__YYYYMM"
This is now the current, date-stamped, people's "master" list.
This copying of the spreadsheet carries over all sorts of adjustments, such as:
- The proper header, with code snippets.
- The appropriate column spacing so header row is no more than 2 sentences high.
- An adjusted spreadsheet so header row does not move as one scrolls up and down through spreadsheet.
- An adjust spreadsheet so it will print well:
- Page set up => Sheet tab:
- Set "Rows to repeat at top:" to: $1:$1
- Under this tab's "Print" section, select "Gridlines".
- Page set up => Sheet tab:
Delete all but the faculty entries
- Use Data => Filter to show all but "*Faculty" rows, and delete those rows. then turn off Filter to show faculty members.
Prepare grad data and paste into "master" spreadsheet
Add "Grad
...
" to title of each person in the "grad" spreadsheet.
- Column "F". Title of column can be "Title".
Concatenate Chair and the Chair type.
- Insert column "C".
- Header is "Chair, type"
- Add code to first blank column, at end ("H"):
- =IF(ISBLANK(A2),"No Chair specified",CONCATENATE(A2,", ",B2))
- Copy-and-paste results into "C" as "Values" only, then delete column H
- Can't use cut, since Paste won't allow "Values" paste off a cut
Cut-and-pasted people data from "grad" spreadsheet into "master" spreadsheet (C-G => A-E)
- Copy out the 5 key columns (C=>G) of data, not including header row.
- Paste below the set of faculty data so that data is not over-written.
Clean up NetID's from Grad data in final sheet
- Filter-select just "Grad" in Title column ("E").
- Add code to column "I" for all Grads:
- =SUBSTITUTE(D2,"@cornell.edu","")
- Reminder: Replace the 2 in D2 with the right number!
- Some of the addresses have leading spaces. To strip them, use this formula instead:
- =SUBSTITUTE(SUBSTITUTE(D2," ",""),"@cornell.edu","")
- =SUBSTITUTE(D2,"@cornell.edu","")
- Paste as "Values" into NetID column, "E".
...
Do the appropriate data concatenations and substitutions
Rearrange columns in staff spreadsheet to match master
- The staff spreadsheet should have its columns rearranged to match the master spreadsheet
- A -> Supervisor
- B -> FirstName
- C -> LastName
- D -> NetID
- E -> title
- F -> CampusAddress
Cut-and-pasted people data from "staff" spreadsheet into "master" spreadsheet (columns A-F => D-I)
In "master", fill out email address and full name columns
...
- Filter-select all by "*Faculty" in Title column ("E).
- Column "G", Email address. Code:
- =CONCATENATE(D2,"@cornell.edu")
- Reminder: Replace the 2 in D2 with the right number!
- =CONCATENATE(D2,"@cornell.edu")
- Column "H", Full name. Code:
- =CONCATENATE(B2," ",C2)
...
Email to NetID conversion
- Reminder: Replace the 2 in D2 with the right number!
Determine changes of people between months
Copy spreadsheets of two months to compare into a directory.
Open spreadsheets and sort them the same. Such as Data => Sort:
- Supervisor/ Chair
- Last name
- Firstname
Copy old data into new spreadsheet.
- Keep only columns A-E (Q: Same for Staff and for Grads?)
- Paste into column "I" (Q: Same for Staff and for Grads?)
Code for column "F". For Grads:
- =MATCH(E2,M:M,0)
For Staff:
- =MATCH(D2,L:L,0)
Code for column "H":
- =IF(F2=G2,"","!!")
Fill in column "G" sequentially, 2 through to the end.
Insert rows ("down") in either data set (the one on the right, or the one on the left) to get them to match up again.=SUBSTITUTE(D2,"@cornell.edu","")