Sorting Text with Worksheet Formula

– Selva V Pasupathy, HSBC Global Resourcing, Hyderabad1

I was trying to see if text sorting can be done with the help of worksheet formula. I tried and I think I am successful to some extent. Sorting text is such a day to day activity, it surprises me why Microsoft hasn’t provided a worksheet formula for doing it.

I know that you always had a choice of autofilter but it requires manual steps. While trying to wrok with countif() formula, I came across with following worksheet array formula which sorts the array. “myArray” is the name of the array this worksheet formula would sort. Please remember, you need to use it as an array formula, i.e., select the whole area where you want the output, and then type your formula and while entering please hold on to ctrl+shift and then press enter.

=INDEX(myarray,MATCH(SMALL(COUNTIF(myarray,”<=”&myarray),ROW(myarray)-ROW(INDEX(myarray,ROWS(myarray),0))+ROWS(myarray)),COUNTIF(myarray,”<=”&myarray),0),0)

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: