Google Sheets conditional formatting with MAX/MIN if duplicate value in another column

user_18280625 0
Viewed 79

I manage an order table with several products in one column and prices in another (range H8:O, I8:J is merged).

Each product can be sold by different suppliers, so the price of a product can vary.

I would like to do a conditional formatting in the price column to know all the time the most expensive and the least expensive price of each product (most expensive in red and least expensive in green).

I tried several formulas but it doesn't work just one seems close to the desired result, here it is:

=N8=ARRAYFORMULA(MAX(SI(NB.SI($H$8:$H;$H8:$H)>1=VRAI;$N$8:$N)))

=N8=ARRAYFORMULA(MAX(IF(COUNTIF($H$8:$H,$H8:$H)>1=TRUE,$N$8:$N)))

Here is a screenshot of the result obtained with this formula:

enter image description here

I tried a formula that identifies product by product, I find the maximum amount in the column but it colors all the same amounts in the column.

=$N8=MAXIFS($N$8:$N;$H$8:$H;"POELE")

Here is what it gives:

enter image description here

Here is a screenshot of the result I would like:

enter image description here

Thanks in advance for your help.

conditional-formatting google-sheets max
2 Answers

Use a formula to check that there are no matching products with a higher price (red):

=COUNTIFS(H$8:H$16,H8,N$8:N$16,">"&N8)=0

and to check that there are no matching products with a lower price (green):

=COUNTIFS(H$8:H$16,H8,N$8:N$16,"<"&N8)=0

enter image description here

Edit 1

If you wanted to highlight only the last occurrence of a highest/lowest price you could add an extra condition like this e.g. for the lowest price:

(COUNTIFS(H$8:H$16,H8,N$8:N$16,"<"&N8)=0)*(COUNTIFS(H8:H$16,H8,N8:N$16,N8)=1)

(note that the anchors to the start of the range are removed on the second Countifs)

Edit 2

In Google Sheets as mentioned you can extend the ranges to the end of the column so the three formulas above would be

=COUNTIFS(H$8:H,H8,N$8:N,">"&N8)=0

=COUNTIFS(H$8:H,H8,N$8:N,"<"&N8)=0

and

=(COUNTIFS(H$8:H,H8,N$8:N,"<"&N8)=0)*(COUNTIFS(H8:H,H8,N8:N,N8)=1)

assuming there is nothing below the formatted range.

user_38949170

If you want for each row use

=(Q9=MAX($Q9:$S9))*(Q9<>"")

AND

=(Q9=MIN($Q9:$S9))*(Q9<>"")

For the whole table use the following

=O9=MAX($O$9:$S$14)

AND

=O9=MIN($O$9:$S$14)

(Do adjust the formula according to your ranges and locale)

enter image description here

user_15277800
Related