sort entire row of data to top of sheet automatically

[bkunes]

i have done a search and assigned either a 1 or a 0 to each row, 1's are
matches. i want to create a sort that will take all the matches and all
their coressponding data in each adjacent column to the top of the sheet. so
in other word if there is a 1 move the entire row to the top of the sheet.
however, i cannot use the <data>, <sort> option because i need the sort to be
automatic, because i will be completeing different searches often, therefore
data will change frequently.


A B C D
E F
0 SC677-0001-54.63 26.3125 2 0.5 45/45
0 SC704-0001-54.63 27.3125 0 3 90/90
1 SC862-0001-54.63 27.3125 0 3 90/90
0 SC884-0001-54.63 27.3125 0 0.375 45/45
1 SC917-0001-77.76 38.88125 0 0 90/90

thanks in advance
--

 

[Don Guillett]

Try using data>filter>autofilter

 

[bkunes]

doesn't auto filter only apply to the current data on the sheet, if the data
changes it will not apply, correct? i need something that does not require
me to manualy click auto filter every time something changes

 

[Don Guillett]

OR
assign 1 and 2 instead and just sort ascending?

 

[bkunes]

i know i can do that and create an auto matic sort using rank and v lookup,
but how do i get the entire row to follow that individual column

 

[RagDyeR]

Just revise and use the array formula I gave you yesterday for your other
"auto-sort" thread.

--

Regards,

RD

 

[bkunes]

i appreciate your help it currently works i kinda am nit picking now to see
if anyone else has any ideas.

 

[bkunes]

i know i can do that and create an auto matic sort using rank and v lookup,
but how do i get the entire row to follow that individual column

 

[Max]

Another play using non-array formulas ..

Source data assumed in cols A to F, key col = col A

In H1:
=IF(A1=1,ROW(),"")

In I1:
=IF(ROW()>COUNT($H:$H),"",INDEX(A:A,SMALL($H:$H,ROW())))
Copy I1 to N1. Select H1:N1, copy down to cover the max expected extent of
source data, say down to row 500? Minimize/hide col H. Cols I to N
auto-returns the required result lines, all bunched neatly at the top.

 

[bkunes]

i think you have the idea of what i want, i got the first formula working but
the second index formula is returning nothing

 

[bkunes]

i think you have the idea of what i want, i got the first formula working but
the second index formula is returning nothing

 

[Max]

i think you have the idea of what i want, i got the first formula working but
the second index formula is returning nothing

For your easy reference, look at this working sample :
http://www.freefilehosting.net/download/3cd57
AutoSort Lines to Top.xls

If your data starts in row2 down, instead of in row1 down,
then just adapt the formulas slightly like this:

In H2: =IF(A2=1,ROW(),"")
Leave H1 blank

In I2, copied across to N2:
=IF(ROWS($1:1)>COUNT($H:$H),"",INDEX(A:A,SMALL($H:$H,ROWS($1:1))))
Select H2:N2, copy down as far as required

 

[Gord Dibben]

Max

That is such a neat method of autosorting.


Gord

 

[Max]

Thanks, Gord.

 

[Bernd P]

Hello,

I suggest this approach which also works with strings:
http://www.sulprobil.com/html/sorting.html

Regards,
Bernd

 

[bkunes]

hey it works wonderful, but how do i still get the "0" rows to show bellow
the "1"'s

 

[Max]

hey it works wonderful, ..

Welcome. But hey, do click the "Yes" button below, won't you? (you forgot to
do this earlier)

.. but how do i still get the "0" rows to show below the "1"'s

Aha, scope creep, they call it.

A new ballgame, which requires tiebreakers.

You could try this slightly revised set-up (still non-array)
on a copy of Sheet1 in the earlier working sample

Source data assumed in cols A to F, key col = col A, as before

In H1:
=IF(A1="","",IF(ISNUMBER(A1),A1-ROW()/10^10,""))

In I1:
=IF(ROW()>COUNT($H:$H),"",INDEX(A:A,MATCH(LARGE($H:$H,ROW()),$H:$H,0)))
Copy I1 to N1. Select H1:N1, copy down to cover the max expected extent of
source data, say down to row 500? Minimize/hide col H. Cols I to N will
auto-return the required result lines (lines with 1's, then those with 0's),
all bunched neatly at the top.