Forum Discussion
Conditional Formatting for Highlight an entire row based on data in another row
Greetings everyone!
I am currently keeping track of my job applications in an excel sheet. Currently, the table looks something like this:
| Company | Position | Position Number | Date | Job Posting | Location | Salary | Current Status |
In the current status of the job, it can list Applied, Rejected, Connected etc. I currently have it so that if the Current Status has text containing "Rejected", the entire row is highlighted as red:
I recognize that it would be better to update the current status of a row from Applied to Rejected instead of what I currently have, which is adding a duplicate row, except the Current Status is Rejected instead of Applied. I have it this way so that I can see the turnaround time.
Anyways, I wanted to know how to highlight the rows that are duplicates of the "Rejected" rows, except for the Current Status being Applied instead of rejected, to be the same red color. After that, I would like any non-highlighted rows to be highlighted as green.
As an example:
| Company | Position | Position Number | Date | Job Posting | Location | Salary | Current Status |
| The Krusty Krab | Chef | 7/5/2023 | some_unique_url.com | Bikini Bottom | Applied | ||
| The Chum Bucket | Chef | R0000001 | 7/5/2023 | another_unique_url.com | Bikini Bottom | Applied | |
| The Krusty Krab | Chef | 7/6/2023 | some_unique_url.com | Bikini Bottom | Rejected |
So, in this example, I currently have the fourth row (including the headers) highlighted as red. I would like to highlight the second row as red, since this corresponds to the fourth row. Then all the remaining rows, except for the headers of course, would be green, so that would be row three.
Not every job application has a URL or position number. If the job application (excel row) has a URL or position number, then it will be unique. The exception is 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. I was thinking of doing some conditional formatting based on matching URLs (since most of my entries have URLs, but not a lot of job applications have postion numbers, for some reason), but I was not sure how to do that. Something like:
URL <- Job Posting WHERE SEARCH("*Rejected*", $H1) > 0
Highlight ALL rows WHERE Job Posting IS URL
Highlight ALL rows WHERE NO Highlight AS GREEN
may work.
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. If anyone knows which is more plausible, or an alternative solution that would be better, please let me know.
Apologies if this wouldn't work. I just wanted to see if this was doable.
<< …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.)
- The Krusty Krab Chef 7/5/2023 some_unique_url.com Bikini Bottom Applied
only red the second row like above rather than row 4th as below
The Krusty Krab Chef 7/6/2023 some_unique_url.com Bikini Bottom Rejected
?
what is your expected result?peiyezhu
So in this case, both rows would be completely redj2
=filter(a2:h100,h2:h100="Rejected")
i2
=iferror(xlookup(e2,n$2:n$100,$n$2:n$100),true)
set condition format formular =not(
iferror(xlookup(e2,n$2:n$100,$n$2:n$100)))
I can not set format condition on my side so that I just provide my thoughts.
See the attached for above formulas.
