how to get the row to top that has the maximum length

[9912271272]

Hi


There is a requirement...i need to get the row on to the top which has the maximum length in characters. For eg.


A B C

1 AB CH

2 ABC KUMAR

3 ABCD PRAVEEN


I need to find the maximum length in the C column and get that row on the top.

Here in this case its PRAVEEN is the max. So i need to have the 3rd on the top.


Thanks in Advance

Praveen

 

[NARAYANK991]

Hi Praveen ,


Finding the length of text in a cell is obtained by using the LEN function ; =LEN(C1) will give you the length of text in cell C1. Copying this formula to all the other relevant cells in column D will give you the length of every relevant cell in column C.


Finding the maximum length of text in column C is obtained by using the MAX function ; =MAX(D1:D3) will give you the maximum length. Extend the range as far as your data.


Seeing where this maximum is located , is obtained by using the INDEX and MATCH functions ;


=INDEX(C1:C3,MATCH(MAX(D1:D3),D1:D3,0)) will return the maximum length string.


Narayan

 

[9912271272]

Thanks for the reply Narayan. Its working as you said. But is there any possibility to bring that entire row to the top. May be using the Macros???


Thanks

Praveen Ch

 

[Luke M]

Modifying Narayank's formula a little, you can now apply it to other column headers as needed.

=INDEX(A1:A3,MATCH(MAX($D1:$D3),$D1:$D3,0))


Put in A1, copy to the right as needed.

 

[Sachin]

Praveen,


All you have to do is use Luke or Narayan's formula in the row where you want the data to appear. The formulas will copy the data to the top row.