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

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).

Tags:

Best Answer

  • SSFeatures
    SSFeatures ✭✭✭✭✭
    Answer ✓

    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!

    Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)

    SSFeatures - The browser extension that adds more features into SmartSheet.

    • Report PDF generator that supports grouped and summarized reports
    • Automatic sorting, sorting with filters, saving sort settings
    • Hiding and unhiding columns, and spell checking

Answers

  • SSFeatures
    SSFeatures ✭✭✭✭✭
    Answer ✓

    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!

    Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)

    SSFeatures - The browser extension that adds more features into SmartSheet.

    • Report PDF generator that supports grouped and summarized reports
    • Automatic sorting, sorting with filters, saving sort settings
    • Hiding and unhiding columns, and spell checking

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

  • SSFeatures
    SSFeatures ✭✭✭✭✭

    You're welcome, glad that the formula worked!

    Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)

    SSFeatures - The browser extension that adds more features into SmartSheet.

    • Report PDF generator that supports grouped and summarized reports
    • Automatic sorting, sorting with filters, saving sort settings
    • Hiding and unhiding columns, and spell checking

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!