/
Excel Tricks

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

Course Scheduling Production Tools
Course Scheduling Production Tools
More like this

Questions? Contact Leta Moser, PLUS Program Coordinator, at leta.moser@austin.utexas.edu