The "people" data from Kevin needs to be aggregated, and added to existing faculty info, for it to be useful.
Kevin provides two spreadsheets whenever he kindly provides us with a snapshot (monthly?). One is the "grad" spreadsheet (from Pat Hine's data), and the other is a "staff" spreadsheet (from Sharon de Roos's data).

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))
• Cut-and-paste results into "C" as "Values" only.

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(DCell_Number,"@cornell.edu","")
• Paste as "Values" into NetID column, "E".

Cut-and-pasted people data from "grad" spreadsheet into "master" spreadsheet (columns A-F => A-F)

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(D40,"@cornell.edu")
• Column "H", Full name. Code:
  • =CONCATENATE(B40," ",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

Current_Faculty201308_oh10