Returning the highest value from a column where another column has potentially duplicate values

ghislop

I'm a first-time poster, thanks in advance for any assistance!

My scenario is that I have many customer sites grouped under parent customers and each customer site is being upgraded in 1 of 3 cohorts (Alpha, Beta, and Charlie) which are occurring in that sequence over the course of a time period. I have added a cheater column to set a numerical value for Alpha (1), Beta (2), and Charlie (3) as I figured it might make a formula easier (i.e. look for the greatest value in the Cheater column)

I have a separate sheet where I have a list of all Parent Customers and need a formula that looks for and returns the highest Cohort for that Parent Customer.

For example, Client A should return "Cohort Charlie", Client B should return "Cohort Alpha", Client C should return "Cohort Charlie", and Client D should return "Cohort Beta" and these values should change if a Customer Site moves to a different Cohort (i.e. if Client D's Site 1 moves from Cohort Beta to Cohort Charlie then the latter should be returned as the value for Client D).

SSFeatures

Hi @ghislop,

If you are okay with always representing 1 as "Cohort Alpha, 2 as "Cohort Beta", and 3 as "Cohort Charlie", then this formula is simple and will work for you.

=IF(
    MAX(COLLECT({Cheater Range}, {Parent Customer Range}, [Client]@row)) = 1, 
    "Cohort Alpha",
    IF(
        MAX(COLLECT({Cheater Range}, {Parent Customer Range}, [Client]@row)) = 2, 
        "Cohort Beta", 
        IF(
            MAX(COLLECT({Cheater Range}, {Parent Customer Range}, [Client]@row)) = 3, 
            "Cohort Charlie", 
            ""
        )
    )
)

The {Cheater Range} is a cross-sheet reference that selects the entire cheater column. The {Parent Customer Range} is a cross-sheet reference that selects that entire column. [Client] is a name that I just made up for your second sheet's client column, because the screenshot doesn't show what the name is.

Let me know if you have any questions!

SSFeatures

Hi @ghislop,

If you are okay with always representing 1 as "Cohort Alpha, 2 as "Cohort Beta", and 3 as "Cohort Charlie", then this formula is simple and will work for you.

=IF(
    MAX(COLLECT({Cheater Range}, {Parent Customer Range}, [Client]@row)) = 1, 
    "Cohort Alpha",
    IF(
        MAX(COLLECT({Cheater Range}, {Parent Customer Range}, [Client]@row)) = 2, 
        "Cohort Beta", 
        IF(
            MAX(COLLECT({Cheater Range}, {Parent Customer Range}, [Client]@row)) = 3, 
            "Cohort Charlie", 
            ""
        )
    )
)

The {Cheater Range} is a cross-sheet reference that selects the entire cheater column. The {Parent Customer Range} is a cross-sheet reference that selects that entire column. [Client] is a name that I just made up for your second sheet's client column, because the screenshot doesn't show what the name is.

Let me know if you have any questions!

ghislop

super, this appears to have the desired result. Nice work and thank you!

SSFeatures

You're welcome, glad that the formula worked!