• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sort data by formula

N

nandhamnk

New Member
Dear All,


I need a formula help on the below data:


RowA

Apple

grapes

pineapple

mango


If i say grapes is "1" in B3,then data should be sorted as below:


Grapes

Pineapple

Mango

Apple


Nee help on this please!
 
Hi

If you have in A2:B5

Column A_____Column B

Apple____________4

grapes___________1

pineapple________2

mango____________3


In C2, write this forumla

Code: 
=INDEX($A$2:$A$5,MATCH(ROWS(A$2:A2),$B$2:$B$5,0))

Then fill down


Regards
 
Hi nandhamnk,


Do you think it is helpful for you?


http://chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/


Kaushik
 
Thanks for your reply mercatog.


But in column B,i have only 1 for B2,rest need to be derived.I need to put sorting based on the above solution.
 
Should be shorter (I'll search)

Code: 
=INDEX($A$2:$A$5,IF(MOD(MATCH(1,$B$2:$B$5,0)+ROWS(A$2:A2)-1,ROWS($A$2:$A$5))=0,ROWS($A$2:$A$5),MOD(MATCH(1,$B$2:$B$5,0)+ROWS(A$2:A2)-1,ROWS($A$2:$A$5))))
 
Thank you for your kind words and glad help you.

No I'm searching for a shorter formula or/and array formula
 
Just trying to improve on mercatog's wonderful formula, I think it can be shortened to:

Code: 
=INDEX($A$2:$A$5,MOD(MATCH(1,$B$2:$B$5,0)+ROWS(A$2:A2)-2,ROWS($A$2:$A$5))+1)
 
@Luke and Maercatog:


This is really excellent....


After closely scrutinizing your formula, I understood how they work...it's really amazing...(before today I did not know MOD function can do such things)


What it needs to come up with such formula? Continuous practice, dealing with different case studies...what else?


Kaushik
 
You must log in or register to reply here.
Back
Top