Highlight Row based on Several Criteria.

[smoskowitz]

Hello --

I am hopig somebody can help me figure this one out. Below is a list with titles, an ID, a Countif total that counted how many times the ID is in the column and an order of importance based on the list below.

Here's what I want to do:

I want to highlight the title with the lowest importance (the highest number) for each ID.

So using the below example, the formula would need to check and count all of the rows that have the same ID. After that it would need to evaluate the importance column and see which number for a particular ID is highest. I would expect to see the rows with the number 5 for importance highlighted for each ID. I have been racking my brain trying to figure this out using formulas, but can't seem to do it.

Title ID Count Importance
CONTROLLER 100000 3 5
CEO/MD 100000 3 1
CFO/FD 100000 3 2
CONTROLLER 105005 2 5
CFO/FD 105005 2 2



Title Order of Importance
CONTROLLER 5
CEO/MD 1
CFO/FD 2
OTHER - CFO 4
OTHER - CEO 3

Thanks for your help on this.

Seth

 

[WinteE]

You can use conditional format for this.

Instead of 'Cell value is' you have to use 'Formula is'

=OFFSET(A1;0;1)=MAX($B$1:$B$5)

Add this code for conditional format of cell A1.

.. where column A holds titles and column B the importance numbers.

Success

 

[smoskowitz]

Thank you, but that doesn't seem to be working for me.

 

[WinteE]

Could you please be more specific ?

Does it give you an error or do you meean an other result ?

In case it's an error you could try :

=OFFSET(A1,0,1)=MAX($B$1:$B$5)