Alphabetize Using Formula

Amy Foeller

Is there a way to easily alphabetize a list using a formula in smartsheet?

In Excel, I would use the simple formula shown below to assign a numerical value to each unique list item.

=COUNTIF([Unsorted Range],"<="&[Specific Cell Value])

This formula doesn't work in smartsheet, so I'm trying to find a solution.

I am working on building a list in a grid (not a report) of all transactions related to a specific job. My formulas pull data in from a much larger list in another sheet based on the criteria of a single job name picked from a dropdown list in this sheet. These formulas pull data directly from the larger list in the larger list's order, but I need to sort THIS list by the vendor name. (See the screenshot of the unsorted list below.)

Since I am utilizing formulas to build this list, I do not want to sort the list manually after every change to the specific job name from the dropdown list. (This "report" is meant for project managers to be able to enter a job name and quickly and easily see a list of all transactions associated with that job. The simpler this process is for them, the better.)

Also, it is not an option to manually sort the larger list of data. This data is pulled in through Data Shuttle and stays in its upload order.

Any help anyone can provide will be appreciated!

David Tutwiler

Can you explain the conditions around why this couldn't be a report, or at least the base sheet that a report pulls off of? I can't think of any way to automatically sort the sheet every time data is updated without going through the API.

However, if you created a report that simply showed all the columns you needed and told it to sort by the QB Vendor column (or even group by that column if that's how you wanted to see it) then that report would automatically sort/group depending on your requirements.

Amy Foeller

Hi David, thank you for your response!

This can't be a report because it's much easier for my not-super-tech-savvy project managers to pick a job name from a drop-down list rather than changing the criteria for a report, honestly. Even if I created a report pulling the data from this sheet, the project managers would still need to input a job name somewhere to see the results they need to see. Again, I need to make this as simple as possible for them.

(I actually already created a MONSTROUS report that shows every single one of our 400+ jobs for the year so far and every single transaction, sorted by a couple different columns. This is great, but it isn't super helpful when a PM is looking to go over a specific job. Ctrl+F is a foreign concept to them, and they would try to print a single job's transactions and would print five million pages instead. Also, they would get confused if they have to change the report settings in order to see what they need to see. Someone inevitably wouldn't change the report settings back to showing all the jobs and the next person would be confused, wondering why this report was only showing one job. Trying to teach old dogs new tricks is NOT an easy task!)

I'm not looking to automatically sort the vendor column; I need a formula that assigns a value to each vendor/instance based on their alphabetical order. The formula will do the sorting any time a new job name is selected.

As you can see in the screenshot below, I can extract a list of the individual vendors, but it's in the same order as my original list. The #UNPARSEABLE error is my attempt at a single alphabetizing formula.

I was hoping to be able to accomplish this alphabetization without having to use a multitude of helper columns that assign values to each character of each vendor name, join those character-values together, and thus obtain an alphabetical list.

Jaykel Torres

Hey @Amy Foeller,

There isn't a singular Formula Function that can perform this action, but as you mentioned, you can use a few helper columns and formulas to return a numerical value based on the letters of a word; this then can technically be sorted alphabetically. I've created the example below for your reference:

• Letter/Letter Value (blue) is used to assign a numerical value to letters
• Word (purple) are the words we would like to sort
• 1st Letter Value/2nd Letter Value/Word Value (yellow) returns the letter value of the 1st and 2nd letter within the word then adds them both together
  • 1st Letter Value =INDEX([Letter Value]:[Letter Value], MATCH(LEFT(Word@row, 1), Letter:Letter))
  • 2nd Letter Value =INDEX([Letter Value]:[Letter Value], MATCH(MID(Word@row, 2, 1), Letter:Letter))
  • Word Value =SUM([1st Letter Value]@row:[2nd Letter Value]@row)
• Word Rank (pink) ranks the Word Value in ascending order
  • =RANKEQ([Word Value]@row, [Word Value]:[Word Value], 1)
• Word Ranks/Word Sorted (green) returns the words according to their rank
  • Word Sorted =INDEX(Word:Word, MATCH([Word Ranks]@row, [Word Rank]:[Word Rank], 0))

I created this example all in one Sheet but I recommend separating these Columns/Formulas into their own Sheets using Cross Sheet References to reduce the amount of Columns in your main Sheet. You can also structure a singular Formula for the Yellow Columns and return the same result if needed (you may modify the formula to evaluate additional letters within the word).

Functions used:

• INDEX
• MATCH
• LEFT
• MID
• SUM
• RANKEQ

I hope this helps!

Jaykel

Linda Gonia

Please see the table below, Dre comes before Gon when alphabetized but using the formula they have the same word value. Can you help me figure out what I did wrong.

Thank you,