Is there a formula where I can identify first item alphabetically in a list?

Randy Saad

For example, if I have a list of names and projects, I would like to identify the first name alphabetically for each project. In the image attached, Alex and Jane should be the first people alphabetically for their projects. Is there a formula that could evaluate a text range and determine which value is first alphabetically?

Order.jpg

Paul Newcome

There isn't a set formula, but you can parse out each character across some hidden helper columns, convert them to a numeric value, then use a MIN/COLLECT type of formula to establish which row to pull from for your INDEX function.

Randy Saad

Thanks Paul. How would you suggest converting a character to a number? Something like nested IF statements or INDEX/MATCH?

Paul Newcome

Either one of them would work. Something to keep in mind though is that there is a "max" number that Smartsheet can use. To keep things consistent, you will need to use two digit numbers for each letter (01 = A through 26 = Z). You will only be able to evaluate on a set number of characters. I will try to dig through my notes to find those details.

Randy Saad

That makes sense. Thanks Paul.

Paul Newcome

It looks like I had noted that the maximum number is going to be 9007199254740991. That's 16 digits long which means using 2 digits per alphabetical character is going to limit you to only alphabetizing based on the first 8 characters.

I feel like (with the proper motivation and some testing) we could break each text string down into sections of 8 characters and assign the sort number to the first 8, assign another sort number to the second 8, so on and so forth then combine the sort numbers into yet another number string which would then allow you to sort on more than just 8 characters.