I work at the front desk of a tutoring and test prep center whose master schedule is a simple Google Sheets file with the schedule for each of our M-Th and Sat. sessions sharing the same spreadsheet. One problem we keep having is that sometimes, because of human error, a student's name will be placed in two different teacher's rows for the same time in the day. Below is an example of what a section of our schedule for a Thursday typically looks like:

204 Teacher Jake 11:00AM - 1:00PM 1:00PM - 3:00PM . . .
205 Alice Allison 12. SAT Math
206 Ben Benson 11. ACT Sci
207 Carl Carlson 9. SAT Math
208 . . .
222 Teacher Kate 11:00AM - 1:00PM 1:00PM - 3:00PM . . .
224 Dan Danson 11. SAT Math
223 Alice Allison 12. SAT Writing
225 Eric Erikson 9. Calculus
226 . . .

What I'm looking for is a way to be able to run a conditional formatting formula or some kind of script in Google Sheets to check if a range contains two cells that contain the same student's name. For instance, I would want to rule/condition to be automatically applied within the range B204:B226 in the above examples so that cells B205 and B223 get flagged or highlighted as containing the same student. Now, because their name is not the only value in those cells, I suppose that technically this formula or script would have to parse cells for what they start with.

I understand that I am asking for help with an extremely specific case with very, very fine parameters, but I truly don't know where else to turn. The main hurdles in my eyes are the ability to check ranges instead of entire columns (every weekday's schedule shares the same columns on the workbook), the ability to check for partial matches (we still need to be able to fill a student's cell with information besides simply their first and last name), and the ability to have this solution be automatic and dynamic such that we don't need to input anything specific for it to work for that name.


1 Answer 1


Use this conditional formatting custom formula rule for the range B204:B226:

=countif(B$204:B$226, regexextract(B204, "([A-z ]+?) \d") & "*") > 1

See countif() and regexextract().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.