...

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

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

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

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