Question concerning sorting numbers by string/dealing with tiebreakers?

user_14270851 0
Viewed 78

I am currently re-educating myself on Google sheets and I was wondering if there was a way to sort a set of numbers by string to deal with tiebreakers (i.e. a win/loss/tie record)

Using the SORT function, I was able to sort things correctly based on wins, but I want to be able to adjust it even further. Currently I have something like this...

  • 3-0-0
  • 1-4-0
  • 1-3-0
  • 1-2-0
  • 1-1-0

when I want to have something like this, where the values with the lowest loss record goes on top instead...

  • 3-0-0
  • 1-1-0
  • 1-2-0
  • 1-3-0
  • 1-4-0

Is there another function or a workaround possible to achieve something like this?

google-sheets-formula google-sheets sorting
2 Answers
=SORT(A1:A5,INDEX(SPLIT(A1:A5,"-"),,1),0,INDEX(SPLIT(A1:A5,"-"),,2),1)
  • SPLIT(A1:A5,"-") by -
  • INDEX(SPLIT(A1:A5,"-"),,1) get first column of the split array
  • INDEX(SPLIT(A1:A5,"-"),,2) get second column
  • =SORT(A1:A5,col1,0,col2,1) Sort A1:A5 in descending order based on column1 and ascending order based on column 2
user_84044530

While in "human language" something like 3-0-0 could be called "a set of numbers" in "spreadsheet language" it's a text value.

To be able to use the built-in sorting functions in Google Sheets, you should split the values into columns, and guess what, there is a built-in function for that:

  1. Select the column
  2. Click on Data > Split text to columns
  3. Select custom separarator and set -

There are a more options, like using a formulas and scripts.

Sample formula

=ArrayFormula(QUERY(
{A1:A5,REGEXEXTRACT(A1:A5,"-(\d{1,})-")},
"select Col1 order by Col2",0)
)

The above formula use a regular expression to extract the numbers between -, then use QUERY to sorth the values in A1:A5 in ascending order by using these values.

user_15954510
Related