Is there a function to find a Max or Min for text?
I know I can do this with a helper column, but I'm trying to avoid that since this sheet copies to a bunch of others and I don't want to have to go and hide the column in all of the downstream sheets.
I have a ID column that consists of alpha and numeric (ex. ENV-032) in sheet A. When someone fills out a form to add a new metric, they select a category that relates to the letters in the ID, and I need to find the highest associated number and add one to get the new metric ID. I have the new entry copying to a helper sheet that has some additional columns to do some other calculations, and moving to a helper sheet that pulls info from the first to fill out the columns needed. Once the columns are filled out the row moves back to the initial sheet and gets copied out to a bunch of sheets downstream.
I've tried the following:
- MAX(COLLECT([metric id], [category],category@row)) //MAX doesn't work with text
- INDEX(COLLECT([metric id], [category],category@row), COUNT([metric id], [category],category@row)) //I have some older metrics in the list that aren't in order and some have different text values so this isn't returning the ID with the highest number every time
Am I missing a function that can easily look at the alphabetical order of text in a range? If not, is there any other way to do this without adding a column to the sheet that has all of the ids?
Answers
-
How are you currently populating the "ENV-032"?
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!
-
@Paul Newcome It came from a historical manual process in google sheets. So it was an uploaded sheet.
-
And I am assuming you don't want the existing values overwritten?
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!
-
Correct...I found a solution that worked for this instance, comparing the the category to the ID to eliminate the older versions and it looks like the largest number of each category is at the bottom even if they aren't in order so I'm just taking the last match.
It seems weird that MAX wouldn't just work alphabetically too though.
-
Can you expand on your solution? I have a similar situation and getting into circular references I am trying to eliminate. I have 2 criteria to match then return the next available 3 digit sequence number.
I have a reference ID that is generated based on XX-YY-001 (XX = 2 digit business line (BL), YY = category (IC), 001 = next available sequence number).CURRENT FORMULA TO CALCULATE NEXT AVAILABLE SEQUENCE NUMBER:
[Seq] =RIGHT(1000000000 + (MAX(COLLECT([Seq Value (converted)]:[Seq Value (converted)], BL:BL, BL@row, IC:IC, IC@row, [Auto #]:[Auto #], <[Auto #]@row)) + 1), 3)
2 main parts to the formula…
…First & Last part part converts the result back into text format of 3 digits.
… Middle part finds the max seq value number above (using the converted to number value column) where BL and IC match current row, but only looks at rows above since new rows are added to bottom, then adds +1 to get next available
-
@Adam Messersmith Try this instead:
=BL@row + "-" + IC@row + "-" + RIGHT("00" + COUNTIFS(BL:BL, @cell = BL@row, IC:IC, @cell = IC@row, [Auto #]:[Auto #], @cell <= [Auto #]@row), 3)
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!
-
@Paul Newcome Brilliant! I was recently introduced the @cell function but i need to learn more on why it works better in certain situations, and also why the criteria is a full x=y format instead of just the criteria. Looks like I have some research to do!
I really appreciate you immediate help on an old thread I revived. -
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 303 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!