...
Oliver has a third "faculty" spreadsheet (data from Joyce Boda); not updated except by hand.
Table of Contents |
---|
...
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.
Replace header row of "master" spreadsheet.
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".
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)
- Reminder: Replace the 2 in D2 with the right number!
- =CONCATENATE(B2," ",C2)
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.
Do the appropriate data concatenations and substitutions
Full name
=CONCATENATE(B2," ",C2)
Email-to-NetID conversion
=SUBSTITUTE(H2,"@cornell.edu","")
Text-copy or Values-copy NetID conversion column (J) into NetID column (D)
Move staff data into "master" spreadsheet
Copy faculty names into "master" spreadsheet
Current_Faculty201308_oh10