Archive for 12628570

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 Comment