Find largest numbers in within column

Brad Mulder

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!

Mark Cronk

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