Highlight largest value cell in range based on other columns

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi All


    is this possible without using VBA code


    column A - consists of dates
    column B - consists of flowers
    column C - consists of numbers between 1.25 and 9.99


    i want to highlight ONLY the highest number in column C when column A and B match.


    sometimes column A and B match many times but sometimes only once


    if this needs to be code i will post in the hire help section.


    thanks for any advice given


    Paul

    Re: Highlight largest value cell in range based on other columns


    Can you please attach a sample workbook? I am struggling to imagine how a date and a flower might match ... :?

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Highlight largest value cell in range based on other columns


    You will need this conditional formatting rule:


    =C1=MAXIFS(C:C,A:A,A1,B:B,B1)


    applied to C1 and set to apply to $C:$C.


    File attached. I hope this works with your version of Excel.

    Re: Highlight largest value cell in range based on other columns


    thank you


    your downloaded file does not work on my excel 2016


    i have tried running it as xls and also changing it to xlxs and neither seem to work


    i have never used conditional formatting so i may be doing it wrong


    i will have a read up on it tonight and try and get it working using your formula


    many thanks Aligw


    Paul

    Re: Highlight largest value cell in range based on other columns


    I am using Excel 2016, so I know it works. In what way does it not work for you? I'll attach another version of the file for you to try.


    You can see it's working from this extract (you had highlighted the wrong number in the tulip section in your sample file):


    Excel 2016 (Windows) 32 bit
    [TABLE="class: head"]
    [TR="bgcolor: #888888"]
    [TH][/TH]
    [TH]

    A


    [/TH]
    [TH]

    B


    [/TH]
    [TH]

    C


    [/TH]
    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    1


    [/TD]

    [td]

    13th

    [/td]


    [td]

    Rose

    [/td]


    [TD="bgcolor: #FCE4D6"]

    3.15


    [/TD]
    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    2


    [/TD]

    [td]

    13th

    [/td]


    [td]

    Rose

    [/td]


    [td]

    2.96


    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    3


    [/TD]

    [td]

    13th

    [/td]


    [td]

    Rose

    [/td]


    [td]

    2.76


    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    4


    [/TD]

    [td]

    13th

    [/td]


    [td]

    Rose

    [/td]


    [td]

    2.86


    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    5


    [/TD]

    [td]

    13th

    [/td]


    [td]

    Rose

    [/td]


    [td]

    3.1


    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    6


    [/TD]

    [td]

    13th

    [/td]


    [td]

    Rose

    [/td]


    [td]

    2.59


    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    7


    [/TD]

    [td]

    13th

    [/td]


    [td]

    tulip

    [/td]


    [td]

    2.94


    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    8


    [/TD]

    [td]

    13th

    [/td]


    [td]

    tulip

    [/td]


    [td]

    2.74


    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    9


    [/TD]

    [td]

    13th

    [/td]


    [td]

    tulip

    [/td]


    [td]

    2.54


    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    10


    [/TD]

    [td]

    13th

    [/td]


    [td]

    tulip

    [/td]


    [TD="bgcolor: #FCE4D6"]

    5.4


    [/TD]
    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    11


    [/TD]

    [td]

    13th

    [/td]


    [td]

    tulip

    [/td]


    [td]

    2.9


    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    12


    [/TD]

    [td]

    13th

    [/td]


    [td]

    tulip

    [/td]


    [td]

    2.78


    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    13


    [/TD]

    [td]

    13th

    [/td]


    [td]

    tulip

    [/td]


    [td]

    2.58


    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    14


    [/TD]

    [td]

    13th

    [/td]


    [td]

    tulip

    [/td]


    [td]

    3.9


    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    15


    [/TD]

    [td]

    13th

    [/td]


    [td]

    tulip

    [/td]


    [td]

    3.7


    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    16


    [/TD]

    [td]

    14th

    [/td]


    [td]

    rhoda

    [/td]


    [TD="bgcolor: #FCE4D6"]

    2.88


    [/TD]
    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    17


    [/TD]

    [td]

    14th

    [/td]


    [td]

    rhoda

    [/td]


    [td]

    2.78


    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    18


    [/TD]

    [td]

    14th

    [/td]


    [td]

    rhoda

    [/td]


    [td]

    2.68


    [/td]


    [/TR]
    [TR="bgcolor: #FFFFFF"]
    [TD="bgcolor: #888888"]

    19


    [/TD]

    [td]

    14th

    [/td]


    [td]

    rhoda

    [/td]


    [td]

    2.58


    [/td]


    [/TR]
    [/TABLE]
    [TABLE="class: grid"]

    [tr]


    [td]

    Sheet: Sheet1

    [/td]


    [/tr]


    [/TABLE]

    Re: Highlight largest value cell in range based on other columns


    Thanks for your patience


    i opened your attached file and it dosent show any highlghted cells


    i went to cf and clicked apply and still no luck


    its obviously something im not doing.


    i am out till late but will try again later


    many thanks Aligw


    Paul

    Re: Highlight largest value cell in range based on other columns


    That can't be so if you really do have Excel 2016.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Highlight largest value cell in range based on other columns


    its a mystery


    according to my account


    i have microsoft office proffesional plus 2016


    should the file you uploaded just work or do i have to enable anything.


    i see the formula on your cf is =C1=_xlfn.MAXIFS(C:C,A:A,A1,B:B,B1)


    which is different from what you said in first post =C1=MAXIFS(C:C,A:A,A1,B:B,B1)


    but i cant get either to work


    very strange


    Paul

    Re: Highlight largest value cell in range based on other columns


    Bizarre, but your version obviously isn't recognising the MAXIFS function. I have the Office 365 subscription - maybe it's not available to you yet?

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Highlight largest value cell in range based on other columns


    i think you may be right


    if i type max in a blank cell i only get max and maxa options


    i will try updating or get latest version


    many thanks

    Re: Highlight largest value cell in range based on other columns


    I'll have another look in the morning, but am away from the PC now. :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Highlight largest value cell in range based on other columns


    its fine you have done enough


    i will update or get the newer version


    thanks you for all your help


    much appreciated


    Paul

    Re: Highlight largest value cell in range based on other columns


    This should work as the CF rule in place of the original formula I gave you:


    =C1=MAX(IF(A:A=A1,IF(B:B=B1,C:C)))

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    Re: Highlight largest value cell in range based on other columns


    Thanks Aligw


    =C1=MAX(IF(A:A=A1,IF(B:B=B1,C:C)))


    works perfect.


    many thanks for your help and for getting to the bottom of why my 2016 version didnt work


    Regards
    Paul

    Re: Highlight largest value cell in range based on other columns


    I think the MAXIFS function must still only be for 365 subscribers. Glad to have helped! :)

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login