...

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.

...

Concatenate Chair and the Chair type.

• Insert column "C".
• Header is "Chair, type"
• CodeAdd 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

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!
• Column "H", Full name. Code:

• • =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

  • • • 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.Current_Faculty201308_oh10