• 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

K

kaushik03

Member
Hi all,


I have data as follows from A1 to A6:


AA

BB

X

CC

X

DD


I want to construct a formula at B1 and drag it all the way down so that it gives me data as follows:


AA

BB

CC

DD


In a word, I want to get the list without X.


Kaushik
 
Kaushik03


At Chandoo.org we can even read minds!

Have a read of the Main Post today at Chandoo.org, http://chandoo.org/wp/2012/10/04/formula-forensics-no-030/
 
Hi Hui,


Thank you for your reply.


Thank you for sharing the link. It’s indeed a week’s reading material for me and at a glance, many things have gone way above my head. May I request you to please share a formula to solve my problem.


However, I tried to understand your first statement(At Chandoo.org we can even read minds!) but it again went way beyond my intellect.


Kaushik
 
Hi all,


I am able to construct the formula. Here it is:


=IFERROR(INDEX($A$1:$A$6,SMALL(IF($A$1:$A$6<>"X",ROW($A$1:$A$6)),ROW(A1))),"") (ctrl+shift+enter)and copy it down...


Please let me know if there is any other better way (I am sure there is) to do this.


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