Find largest numbers in within column

Options

Hi all,

Please see the attached photo, is there a formula/way in which it will showcase the highest number for each segment? For example: KS-133, BELM-109, AWG-107, etc.

Thank you!

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi @Brad Mulder ,

    I don't know about showcasing the highest number but there is a way to find the highest number. Smartsheet is going to treat your FileNumber as text which makes your ask harder.

    The solution I'm thinking of is to add 2 columns to your sheet - 1 for the the Alpha portion of your FileNumber [ =LEFT([Primary Column]@row, (LEN([[FileNumber]@row) - FIND("-", [FileNumber]@row))) ] and 1 for the Numeric [ =VALUE(RIGHT([FileNumber]@row, (LEN([FileNumber]@row) - FIND("-", [FileNumber]@row))))) ] .

    Then in another column use a MAX(COLLECT())function to find the largest number with a specific alpha. As an example to find the maximum number for BELM the formula would be =MAX(COLLECT([Numeric]:[Numeric}, [Alpha]:[Alpha], = "BELM")). You could build a small table at the top of your sheet with your Alpha values in a column and the highest number in the other.

    There are lots of smart people on here. Someone may have a better answer.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!