How to get distinct values from a column, then shows their maximum number?

How to create a formula that can get me the distinct name list from the left table then get their max values?

Please help me

Best Answer

«1

Answers

  • Frank Falco
    Frank Falco ✭✭✭✭✭✭

    Put this formula into each cell in Max Number column

    =MAX(COLLECT(Number:Number, [List of Vehicles]:[List of Vehicles], [Distinct name]@row))


    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

  • Gordon
    Gordon ✭✭✭✭✭
    edited 06/29/20

    @Frank Falco Thank you for the formula! As an add on, I converted MAX to SUM, and added an IF logic as a prefix, to only add totals where the Distinct Name was completed in the summary table


    Works perfectly!

  • @Paul Newcome

    I'm using the first helper column to create numbers for distinct entries in a column, It does well for anything that has text in front of it, but skips rows with # only that are unique. Is there a way to fix this to grab those as well?

    For example SWFZ160493 will get a # , but 111152 will be skipped. It is showing as a string and not a value.

    Thanks,

    Jeff


  • Hi There,

    I can't get it work.

    What is wrong with my formula?


    =IF(COUNTIFS(Description2045:Description2054, Description@row) = 1, COUNT(DISTINCT(Description2045:Description2054)))


    Thanks for answer,

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Jeff Bunker Create a text/number column and then use this as a column formula:

    =[Column name]@row


    Change [Column Name] to reflect whichever column contains "SWFZ160493" and "111152".


    Then reference this new column in your formula that generates your numbers.


    The problem you are running into is that your data is not all the same format. You have some numbers and some text. All data values must be the same. Using this helper column converts everything to a text string and should get the job done for you.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Juraj Slezak Are you getting an error or an incorrect result? What exactly are you trying to accomplish with your formula?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome this has greatly helped me towards achieving a sheet listing distinct values (customer names in my example). I have adapted your example to show the SUM not MAX . My challenge is that my master sheet has to be populated by smartsheet 'Forms' hence I the 'help column' or 'Number column' for me, cannot ne prepopulated manually as you describe "

    I have tried to make the properties of my 'Number' help column as Auto-number, but sadly that stops the formula from creating the distinct /unique value (customer name).

    Any suggestions please?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Andy Trickett Are you able to move the distinct list starting with the Number column to a separate sheet? That would allow you to prefill as many rows as you need to.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome Yes, a really helpful nudge in the right direction thank you. I have created a new sheet solely to hold my growing 'DISTINCT' list, alongside my 500 rows of prepopulated consecutive NUMBERS (help column) Which I actually used the sheets PIMARY COLUMN for. I left the COUNT (help column) in the 'Master sheet' which has the smartsheets Form feeding it. All work great & KPIs shown in a Dashboard. 😀 thanks again.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Andy Trickett Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Are there any limitations on the "distinct" formula? I'm using it in smartsheets and seems to stop pulling in distinct data after row 273

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Veronika Young It depends. How exactly are you using it?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome it turned out to be a limitation of 4,000 characters in the cell, not an issue with the formula.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Veronika Young That was going to be my first guess. I am glad you got it figured out. Were you able to implement a solution?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!