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
--

Try using datafilterautofilter

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bkunes" wrote in message
...
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
--

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" wrote:

Try using datafilterautofilter

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bkunes" wrote in message
...
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
--

OR
assign 1 and 2 instead and just sort ascending?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bkunes" wrote in message
...
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
--

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
--



"Don Guillett" wrote:

OR
assign 1 and 2 instead and just sort ascending?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bkunes" wrote in message
...
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
--

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

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"bkunes" wrote in message
...
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
--



"Don Guillett" wrote:

OR
assign 1 and 2 instead and just sort ascending?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bkunes" wrote in message
...
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
--

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



"RagDyeR" wrote:

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

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"bkunes" wrote in message
...
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
--



"Don Guillett" wrote:

OR
assign 1 and 2 instead and just sort ascending?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bkunes" wrote in message
...
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
--

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

--



"bkunes" wrote:

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
--



"Don Guillett" wrote:

OR
assign 1 and 2 instead and just sort ascending?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bkunes" wrote in message
...
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
--

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,RO W())))
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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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" wrote:

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,RO W())))
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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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" wrote:

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

--



"bkunes" wrote:

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
--



"Don Guillett" wrote:

OR
assign 1 and 2 instead and just sort ascending?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bkunes" wrote in message
...
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
--

"bkunes" wrote:
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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max

That is such a neat method of autosorting.


Gord

On Fri, 22 Feb 2008 16:54:00 -0800, Max wrote:

"bkunes" wrote:
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

Thanks, Gord.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Max

That is such a neat method of autosorting.


Gord

Hello,

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

Regards,
Bernd

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



"Max" wrote:

Thanks, Gord.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Max

That is such a neat method of autosorting.


Gord

"bkunes" wrote in message
...
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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---