Excel Tricks

*To separate first/last names on attendance rosters on U:

For rosters, download

Insert one column to right of condensed (last first) name combo

Data

Text to columns

Delimited, next

semicolon

Destination spot= next cell

And then with the power of excel, the names magically separate into last in one column, and first in the neighboring column.  Wow!

*to combine first (column A) and last name (column B) from separate columns into one column

=A1&" "&B1

*To keep column and header constant as scroll across frame:

Identify cell you want to pivot from, highlight single cell

Window, freeze pane.

Voila!

*remove duplicate applications:

of those completed apps, eid/email

data, advanced filter, copy to new location, check unique records only

*excel godsend to identify those Preceptor candidates that have not been chosen.

column A: email of all preceptors that have applied (minus duplicates).

column B=  " =IF(COUNTIF(C$4:C$7,A4),"",A4) "

column C= email of all chosen preceptors

where A4 is the first cell of A applied preceptors, keep $ signs in, B is the middle filling in column of those that have not been selected, C$4:$C$7 is the column of chosen preceptors, and A4 is what comparing to .  THIS equation is gold.  do not loose it, and make sure you understand how to use it.

column C: all preceptors that have been selected

*note: if there are typos in emails this won't catch them.  embarrassing fielding questions from preceptors saying, hey, i got in...didn't i?

see JWs example on U:, sanger, PLUS, hiring, preceptors, PLUS separator XL function.xlsx

*end of sem sort

=IF(total session attendance >=1, eid column,"")

one column over

=IF(ISTEXT(one column over), total session attendance,"")

copy and special paste (values) all into fresh, label course/prof

sort by # B column (highest to lowest), then by prof if desired

*to identify uniques

=IF(at least one column >=1,eid column,"")

*# sessions

=(# sessions column)

or

=IF(ISBLANK(unique column)=TRUE, "", # attended column)

*Instructional hours

=# sessions*1.5 or 1hr

*to remove duplicates to identify unique eid participants

data

remove duplicates

• to add a new line within an excel cell
   
  Cmd+Option+Enter.

find/highlight duplicates: select eid column

home, conditional formatting (visual grid), highlight cells rules, duplicates values, ok.

will highlight repeat eid values

filemaker date/time formatting

What I have found my process has been (outlined) includes:

Correctly formatting the google attendance data by formatting date column 00/00/0000

adjust study group start/end time to be in military time (per filemaker key instructions) with PM dates with +12.  ex 4pm= 16:00:00; 10am=10:00:00)

create copy and paste-able columns combining the date and start/end times (two different columns) using: =TEXT(A2,"mm/dd/yyyy")& " " &TEXT(D2,"hh:mm:ss")

where A2 is the date correctly formatted and D2 is the time correctly formatted

then copy and paste eid columns, date/time start and end columns, and fill in the PC eid column and class columns.