...

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.

...

• 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))
• CutCopy-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)

...

• Filter-select just "Grad" in Title column ("E").
• Add code to column "I" for all Grads:
  • =SUBSTITUTE(DCell_NumberD2,"@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(D40D2,"@cornell.edu")
    • Reminder: Replace the 2 in D2 with the right number!
• Column "H", Full name. Code:
  • =CONCATENATE(B40B2," ",C40)

====================

Cut-and-paste email addresses from NetID column (D) to Email address column (H).

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

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