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
-
-
@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?
-
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.
Help Article Resources
Categories
Check out the Formula Handbook template!
