Sort entries in one column dynamically & extract data based on rank

Moderator note: This thread originally posted in Excel Formulas & Functions. Moved here at OP's request.
-- FlameRetired

Greetings!!
I'm facing a problem where I have to extract data from Sheet1 to Sheet2.

Sheet 1 contains item code, it's quantity & other conditional data in columns C, D, E which may be delivered using formulas or entered manually.

If all conditional data in C, D, E column for that item code is OK, then "clear " appears in column F in that row.

As soon as " clear" appears in the cell, data in column A, B from this row should be extracted to sheet2.

Later if conditional data in either of column C, D, E for that row is not met, extracted data should be removed from sheet2 & data in sheet 2 should be rearranged without leaving blank rows in sheet2.

Attached is a sample file where I've shown the expected scenarios.

I've been able to solve data extraction & rearrangement of data in sheet2 by using formula( also in the attached file), but only if I get a rank in sheet1, based on when "clear" appears in sheet1 in F column of a particular row. This part I'm unable to solve.

In A3 of sheet 2 then copied across

Please Login or Register  to view this content.

Perhaps this could be a consideration. Instead of marking one in a cell when a condition is met use a timestamp.
The keyboard shortcut for dates is Ctrl + ;
The keyboard shortcut for times is Ctrl + Shift + ;
The keyboard shortcut for date and time (as modeled) is Ctrl + ; then space then Ctrl + Shift + ;
The option you use would depend on how closely in time the conditions would be met.
Column F is populated using: =IF(AND(C3>0,D3>0,E3>0)=TRUE,"clear","")
Column G is populated using: =IF(F3="","",MAX(C3:E3))
Column H is populated using: =IF(F3="","",RANK.EQ(G3,G$3:G$11,1))
The formulas in columns J:K now reference column H.
Test by deleting the date time stamp in D3
Let us know if you have any questions.

Originally Posted by GAURAVVOHRA

... Also just before seeing this post, I started a new query for the same problem in VBA forum.
Hope you can help.

Please don't do that. It is called double posting. If you feel the existing thread may call for a VBA solution state so in this (the original) thread. If you feel it is more appropriate for the VBA section ask an Administrator or Moderator to move it for you.

Below is the canned message for double posting. In this case I will close the duplicate and let this one stand until you deem it otherwise.

I'm sure you understand.

Administrative Note:

Welcome to the forum.

Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here. I am, therefore, closing this thread, but you may continue here in the original thread: link to be added here

I am not versed in VBA. To get a code based solution, you may wish to ask Dave or one of the other moderators/administrators to move the thread over to the Excel Programming / VBA / Macros forum for you.
I hope that you have a blessed day.

Done.______________