Re: Dynamic sorting of rows of data.

From: Hari (excel_hari_at_hotmail.com)
Date: 07/22/04 

Date: Fri, 23 Jul 2004 00:06:21 +0530

Hi Bernie,

Thanx a lot . You have solved it a very neat way. Great piece of Logic.

Regards,
Hari
India

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:e3ZG7F4bEHA.2352@TK2MSFTNGP09.phx.gbl...
> Hari,
>
> Use a helper column of formulas, with a formula like
>
> =MAX(V2,M2,U2,L2)
>
> and copy down to match your table, then sort based on that formula.
>
> HTH,
> Bernie
> MS Excel MVP
>
> "Hari" <excel_hari@hotmail.com> wrote in message
> news:Ov50nR3bEHA.1048@tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I have data in Column B thru V.
> >
> > Row no 1 is header row.
> >
> > Data extends till lets say row number Z where Z keeps on changing
> depending
> > on the amount of data. For calculating the value of Z, I use the no of
> > entries in column F as the basis ( as it could be possible that cell
B59,
> C
> > 59 has no data but F59 will have data) This Z is calculated within the
VB
> > code and designated as the variable "rowcount".
> >
> > Now comes the real ( and difficult part)...
> >
> > I want to sort the above data.
> >
> > I recorded the macro as per my sorting specifications and got the
> following
> > code:
> >
> > Rows("2:" & rowcount - 1).Select
> > Selection.Sort Key1:=Range("U2"), Order1:=xlDescending,
> > Key2:=Range("M2") _
> > , Order2:=xlDescending, Key3:=Range("L2"), Order3:=xlDescending,
> > Header _
> > :=xlGuess, OrderCustom:=1, MatchCase:=False,
> > Orientation:=xlTopToBottom _
> > , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
> > DataOption3:= _
> > xlSortNormal
> > Selection.Sort Key1:=Range("V2"), Order1:=xlDescending,
> > Key2:=Range("U2") _
> > , Order2:=xlDescending, Key3:=Range("M2"), Order3:=xlDescending,
> > Header _
> > :=xlGuess, OrderCustom:=1, MatchCase:=False,
> > Orientation:=xlTopToBottom _
> > , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
> > DataOption3:= _
> > xlSortNormal
> >
> > Im sorting 2 times because excel's normal application has "Sort By"
> feature
> > only for 3 columns at a time. But in my case I have to sort data
depending
> > on data in 4 columns hence did sorting 2 times. Here my sorting is done
> with
> > Column V having the highest priority then U then M and lastly L.
> >
> > Basically I have dates in Column V,M,U and L.
> >
> > Problem is there are occasions when dates in column V will not be
greater
> > than date in the other 3 columns. For those variable times I want to
> > determine the row which has the maximum of the dates in the range of
> column
> > L2 to V "rowcount" ( only column L, M, U and V to be used for
determining
> > maximum date and rowcount is the maximum number or rows to which the
data
> > extends) then use this information and cut this particular row and move
it
> > to row number 2 which is the top row and similarly Now I want to find
that
> > row which has the maximum of dates in the range L3 to V "rowcount" and
> then
> > cut that whole row and move it to insert it in place of row number 3 and
I
> > want to keep doing this till the last row which is rowcount.
> >
> > Please guide me for the same.
> >
> > Regards,
> > Hari
> > India
> >
> >
>
>

Relevant Pages