For the range C2:Z100 we apply the following formula to find duplicate students' enrollments using conditional formatting:

=COUNTIFS($C:$C,C2 , $D:$D,D2)>1

(where column C is the last name and D is the first name e.g. JohnSmith)

Unfortunately, we only get single duplicate cells in column C highlighted.

But... We want to have the entire row highlighted.

We have tried different variations and suggestions from the site as here and here or here (as well as many others). Nothing seems to work for the entire row. Also tried with COUNTIF , =AND (COUNTIFS($C:$C,C2 , $D:$D, D2)>1), COUNTA.No luck.

@user0 I know a sheet would help but students data are sensitive and would have to prepare a new one. On the other hand I believe the answer should be "quite simple". We are just stuck.
– marikamitsosSep 2 '18 at 0:58

1

there is no need for the real sheet with real sensitive data... a mare dummy with faked data is more than enough. what matters is time - including sample data will save time for both sides (your "wait time" and our "time we spend recreating the whole thing from scratch and guessing for unknown variables omitted in question") and fasten up the answer you seek
– MARK MY ANSWERSep 2 '18 at 2:06

1 Answer
1

conditional formatting is based on the single reference which is then multiplied on a given range. to assure reference is single you need to lock it down with $ symbol. in this particular case, you need to lock columns because conditions are static column-wise and keep them dynamic row-wise. therefore: