...

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.

Replace header row of "master" spreadsheet.

Cut-and-paste header from previous version of "master" spreadsheet.

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," ","")

Do the appropriate data concatenations and substitutions

Full name

=CONCATENATE(B2," ",C2)

Email-to-NetID conversion

...

• • • • ,"@cornell.edu","")

Text-copy or Values-copy NetID conversion column (J) into NetID column (D)

...

• 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

...

Copy faculty names into "master" spreadsheet

Current_Faculty201308_oh10

Copy previous "master" list.

Delete all but faculty entries

(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 againUse Data => Filter to show all but "*Faculty" rows, and delete those rows. then turn off Filter to show faculty members.