Sort data by excel formula


 

Excel provides SMALL and LARGE formula to get sorted numeric values , but sometime we need our string/text data to be sorted by formula only.

Though, there is an inbuilt sorting feature available in excel, but here I am going to demonstrate a formula.

So, Lets say you have your data list in Range “A1:A10”, Better to create a name instead of using direct reference into the formula. so I have create a name range that is “myList”

=INDEX(MyList,MATCH(SMALL(MMULT(N(MyList>TRANSPOSE(MyList)),ROW(MyList)*0+1),ROW(MyList)),MMULT(N(MyList>TRANSPOSE(MyList)),ROW(MyList)*0+1),0))

 

It might be looking a long formula to some of you,but believe me it is not,  because red part is same. Ok,lets evaluate this.

The base inside this formula is  this ,

=N(MyList>TRANSPOSE(MyList))

this formula compares each value with each value in the list and create a two-dimensional array

 T1

 if you look at the 0 in B2 it mean that 10 in A2 is not greater than 10 in B1, and if you look at 1 in B3 , it means 12 in A2 is greater than B1, and so on. the next step is to sum this array by rows. So we will MMULT here with another array. See the  formula down below to get that second array, this should have the same number of row element as our first array but all the element would be 1

=ROW(MyList)*0+1   = {1;1;1;1;1;1;1;1;1}

and now we have two matrix to multiply , so here we use MMULT() function

=MMULT(N(MyList>TRANSPOSE(MyList)),ROW(MyList)*0+1)

After multiply that two matrix it will give a result like this table. 

t2

Now we just need to sort these numbers, and we will use SMALL() function to do that. So here is the next formula

=SMALL(MMULT(N(MyList>TRANSPOSE(MyList)),ROW(MyList)*0+1),ROW(MyList))

this will return these sorted numbers

={0;1;2;3;4;5;6;7;8}

Next , we need to match the correct index for all these elements from the above list.

=MATCH(SMALL(MMULT(N(MyList>TRANSPOSE(MyList)),ROW(MyList)*0+1),ROW(MyList)),MMULT(N(MyList>TRANSPOSE(MyList)),ROW(MyList)*0+1),0)

this will return these index numbers

={3;1;2;6;7;8;4;5;9}

Now we got the index and we just need to get the Text from our original list

=INDEX(MyList,MATCH(SMALL(MMULT(N(MyList>TRANSPOSE(MyList)),ROW(MyList)*0+1),ROW(MyList)),MMULT(N(MyList>TRANSPOSE(MyList)),ROW(MyList)*0+1),0))


and here is your sorted list

t3

 

Thanks for reading

Rajan verma

7838100659

Leave a Reply

Fill in your details below or click an icon to log in:

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