Responsibility and Respect
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.
Related content
Questions? Contact Leta Moser, PLUS Program Coordinator, at leta.moser@austin.utexas.edu