Monday, July 8, 2013

Google Spreadsheet sort in case-sensitive ascending order

Question:

( by Nighthound )

I have a list of text entries in a column that I wish to sort in case-sensitive ascending order, with capital letters before small letters.

For example, I want to sort:

Abcd
ASDF
aaaa

and get:

ASDF
Abcd
aaaa

However, Google Sheets ignores case when sorting in ascending order and gives this result:

aaaa
Abcd
ASDF

 What can I do to achieve my desired result?

Solution:

Your desired result could be achieved in many ways, but the easiest and according to me the best way is to have a custom function.
Custom function means you have to built the function of your own in the scripts. And I have coded a script, and here it is:

function ksort()
{
  if (arguments.length < 1)
    return "#NA Please input range to sort";
  else if (arguments.length > 1)
    return "#NA Input Can't be more than one arguement";
  else if(arguments[0][0].length == 1)
    return arguments[0];
  else
    return arguments[0].sort();
}

Now, put the above code in your script editor and you are done.

Have a look at the following screenshot:



Now put the formula as:
=ksort(yourRangeToSort)

that is:
=ksort(A2:A9)
(for the example shown above in the screenshot).



And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

No comments:

Post a Comment