Oliver has a third "faculty" spreadsheet (data from Joyce Boda); not updated except by hand.

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".

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","")
• 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!
• 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:

1. Supervisor/ Chair
2. Last name
3. 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.