Forum Discussion
Conditional Formatting for Highlight an entire row based on data in another row
<< …how to highlight the rows that are duplicates of the "Rejected" rows, except for the Current Status being Applied instead of rejected… >>
But the Status value is not the only one to be used in determining a duplicate. To determine turnaround time, you need different Date values.
<< …if the status is Rejected, then the row has exactly one duplicate with the current status being applied instead, just like in the example above. >>
But I would expect that if an interim status of Connected were recorded, you could have at least three rows.
<< I could also just assign a unique position number to any job application that does not have one, and maybe do matching on position numbers. >>
There is a small possibility that two employers could use the same position number for their respective positions. I recommend just assigning a unique number of your own for every position. That's what I have done in column A in the attached workbook. That also allows for simpler, more efficient formulas for determining highlighting:
for highlighting as Rejected:
=COUNT( FILTER( $A$2:$A$999, ($A$2:$A$999=$A2)*($I$2:$I$999="Rejected") ) ) > 0
for all others:
=AND( LEN($A2) > 0, COUNT( FILTER( $A$2:$A$999, ($A$2:$A$999=$A2)*($I$2:$I$999="Rejected") ) ) = 0 )
(Spaces are included for readability only.)