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

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:

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:

Here is a screenshot of the result I would like:

Thanks in advance for your help.

CodePudding user response：

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

CodePudding user response：

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)