|
sort entire row of data to top of sheet automatically
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 -- |
sort entire row of data to top of sheet automatically
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 -- |
sort entire row of data to top of sheet automatically
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 -- |
sort entire row of data to top of sheet automatically
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 -- |
sort entire row of data to top of sheet automatically
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 -- |
sort entire row of data to top of sheet automatically
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 -- |
sort entire row of data to top of sheet automatically
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 -- |
sort entire row of data to top of sheet automatically
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 --- |
sort entire row of data to top of sheet automatically
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 --- |
sort entire row of data to top of sheet automatically
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 -- |
sort entire row of data to top of sheet automatically
"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 --- |
sort entire row of data to top of sheet automatically
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 |
sort entire row of data to top of sheet automatically
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 |
sort entire row of data to top of sheet automatically
Hello,
I suggest this approach which also works with strings: http://www.sulprobil.com/html/sorting.html Regards, Bernd |
sort entire row of data to top of sheet automatically
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 |
sort entire row of data to top of sheet automatically
"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 --- |
| All times are GMT +1. The time now is 04:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com