...
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.
...
- The proper header, with code snippets.
- The appropriate column spacing so header row is no more than 2 sentences high.
- An djusted 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:
...
- 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
Cut-and-paste email addresses from NetID column (D) to Email address column (H).
Do the appropriate data concatenations and substitutions
...
- 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!
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.
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