Alphabetize Using Formula

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!

Find more posts tagged with

Formulas

Accepted answers

All comments

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:

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,

Douglas Martin

Sorry if I am late to the party. Just tried out this solution.

My result did not make a Value/Rank distinction between opposite pairs.
AB vs BA, ETC.,

ALSO, Is there a way to set row ranges (IE $33:$62)?
I was trying to use the formula for multiple sets on a shared column, but the formula yields

Thanks

Douglas Martin

Hi gang,
I tested this out more and here are my findings:

This does not account for opposite pairs (IE AB = BA, etc.)
I was able to make this more granular by changing the values
First Name (1st letter value) =MID(NAME@row, +1, 1)
value =INDEX(SORT:SORT, MATCH(LEFT([N1]@row, 1), LETTER:LETTER))
Second Name (1st letter value) =MID(NAME@row, FIND(" ", NAME@row) + 1, 1)
(.) value =IF([N2]@row <> "", VALUE("." + INDEX(SORT:SORT, MATCH(LEFT([N2]@row, 1), LETTER:LETTER))), "")
Second Name (2nd letter value) =MID(NAME@row, FIND(" ", NAME@row) + 2, 1
(.0) value =IF([N3]@row <> "", VALUE(".0" + INDEX(SORT:SORT, MATCH(LEFT([N3]@row, 1), LETTER:LETTER))), "")
Second Name (3rd letter value) =MID(NAME@row, FIND(" ", NAME@row) + 3, 1)
(.00) value =IF([N4]@row <> "", VALUE(".00" + INDEX(SORT:SORT, MATCH(LEFT([N4]@row, 1), LETTER:LETTER))), "")

I also sorted the RANK field to be ascending =IF(VALUE@row <> "", RANKEQ(VALUE@row, VALUE$64:VALUE$93, 1), "")

CONCLUSION: While this does provide value numbers that are more unique, it still fails if names happen to be the same or too similar.

Neil Egsgard

I just had this issue. I successfully used this formula to get the alphabetized rank of each row.

=COUNTIF([Column name]:[Column name], "<=" + [Column name]@row)

I needed the alphabetized rank because:

I need to update the order of the values on another sheet using DataMesh
New values could be added at any time and they may not be in alphabetical order.
I am using the Alphabetized rank as the key for the DataMesh

Identical values in [Column name] will receive the same alphabetical order rank.

Neil Egsgard
Business Solutions Architect
Southern Alberta Institute of Technology

KristofKalocsai

Hi everyone,

I had a similar problem, and this thread was helpful in providing some basic insights. As it was pointed out, assigning numerical values to the letters and then determining the points of the 1st, 2nd, etc. letters of words is a good start, however if you just add those values, there are a number of problems that lead to inaccurate sorting. Here is a potential way around that, that makes the previously suggested solution work well:

When you have your letter values, multiply them with various powers of 10. Since we have 26 letters in the alphabet, an increment of x 100 - or 10^2 - should be sufficient. For example

1st letter value: 1

2nd letter value: 7

3rd letter value: 21

4th letter value: 15

Your word value formula should be then:

=[1st letter value]@row * 10^6 + [2nd letter value]@row * 10^4 + [3rd letter value]@row * 10^2 + [4th letter value]@row * 1

That will translate to =1 * 10 ^ 6 + 7 * 10 ^ 4 + 21 * 10 ^ 2 + 15 * 1 which equals 1072115

Notice, that all letter value digits are present in the final word value, which allows you to properly rank the words based on this number.

Things to consider:

Multiply the first letter with the biggest number, the 2nd with the 2nd biggest, and so on.

Smartsheet has a limit on the longest number it can work with in terms of characters, so if you have an #OVERFLOW error, you're over the limit.

To reach maximum number of letters for ranking, use 10 to the negative power. In my current solution, my highest multiplier is 10^10, and the lowest is 10^-6. However, I already suspect the last letter value is being rounded up because of the character limit.

I hope this was helpful. Good luck!

Amy.Mizzi.RP

https://community.smartsheet.com/discussion/comment/476472#Comment_476472

I've worked in Smartsheet for 6 years and never knew that text values could be ranked so easily.

I used the syntax below and it worked beautifully. Thanks!

=COUNTIF([Column Name]:[Column Name], <=[Column Name]@row )

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.