Is there a function to find a Max or Min for text?

sharkasits

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:

1. MAX(COLLECT([metric id], [category],category@row)) //MAX doesn't work with text
2. 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?

Paul Newcome

How are you currently populating the "ENV-032"?

sharkasits

@Paul Newcome It came from a historical manual process in google sheets. So it was an uploaded sheet.

Paul Newcome

And I am assuming you don't want the existing values overwritten?

sharkasits

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.

Adam Messersmith

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

Paul Newcome

@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)

Adam Messersmith

@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.

Paul Newcome

@Adam Messersmith Here is a help article for @cell:

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

.