Dynamic sorting of rows of data.

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


0
excel_hari (160)
7/21/2004 10:45:18 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
584 Views

Similar Articles

[PageSpeed] 19

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


0
Bernie
7/22/2004 12:18:53 AM
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
> >
> >
>
>


0
excel_hari (160)
7/22/2004 6:36:21 PM
Reply:

Similar Artilces:

service calendar in MS Dynamics 4.0
Does anyone knows how we can achieve the following requirement with MSCRM 4.0 In the service calendar in MS Dynamics 4.0 you are able to create new appointments and service activities. Appointments and Service Activities which are created will be present in the Service Calendar. To see all details of such a record[Subject(default Value)] you must open it. But by moving your mouse on the concerning appointment or service activity, a text-box-view appears with primary information. With appointments the content of the following fields is displayed: 'show time as', 'subject...

Extracting Data from Table
I am working on a table where I am trying extract certain data and placing it into another table on the same sheet. Everytime I try, I highlight the criteria and click advanced under filter and select the correct range and press okay nothing changes or shows up in the new table. Help please! Thanks, Alisa ...

dynamic resource
Hi, Anybody know how to load e.g. bitmap into resource dynamicaly? The bitmap file will be downloaded by application itself , so it has to be somehow inserted into resource and displayed. thanks What do you mean by "into resoure dynamically"? Do you want to load a ..bmp file and display it? If yes, take a look at GDI+, which is by far the easiest way to achieve this. ---------- Ajay Kalra ajaykalra@yahoo.com I misspelled this, yes I'd like to load new bitmap on the fly. But resource file looks like is static. I use ATL/MFC applicatation. "Ajay Kalra" <aja...

Data Migration Manager apparently does not have correct Mappings for BCM 2007 Data
I have been trying unsuccessfully for quite some time to migrate BCM 2007 data to CRM 4.0 via the Data Migration Manager using the existing mapping for BCM 2007. However, once I am in there, I have to re-map all data, and even after that, I am unable to successfully import the data. Has anyone had the same issue, and if so, is there a different mapping I should be using? Thanks. what are the failure errors are you getting? are you mapping any data to a lookup field? it is usually a data related issues! -- Kamal Hitari MBSS, MCNPS, MCTS 2B.net Ltd http://kamalhitari.blogspot.com jdmic...

Dynamic sum in Chart
Hello: So I've got this pivot chart that I've created, and it's great, EXCEPT when I select different categories to show up, I want to show a SUM of the categories, not the values of each category. I would appreciate any help/advice you may have. Thank you! Michelle In the pivot table you can select different thing like count and SUM etc. If you right click in the upper left vacant square and chose field settings, then select what you want to see in your data section. Misha wrote: > Hello: > > So I've got this pivot chart that I've created, and it's great...

Maximum Row Height Greater than 409.5
I read and Googled how I can increase the height of a row in Excel. Someone suggested merging two cells together. When I tried that, Excel came back and warned me that it would inly keep the Upper Left-Hand column and it didn't lie. I have data in columns A thru E. How can I keep the data and make the row height more than 409.5??? The font size is already really small. Any help would be greatly appreciated. Thanks in advance for your review and hopeful for a Reply. PSULionRP what exactly are you trying to do? If you have large amounts of text, maybe a table in Wor...

sort sheet with Turkish Characters
I want to sort a column which contains turkish characters. A sample sort output is as follows: ab ça ce çg ia ıe öa oe The correct form should be ab ce ça çg ıe ia oe öa since ç comes after c, i comes after ı and ö comes after o. Please help me how to achieve this result. Thanks in advance Talat Oncu PS: Solution for Excel XP and Excel 2003 are welcome Microsoft sells a Turkish Language Pack for $25.00. However, I don't know whether the pack includes a sorting capability. see... http://buy.trymicrosoftoffice...

Tab seq dynamically...
I have a button which added dynamically to a property sheet. Now for setting the tab sequence with this new button, Am using SetWindowPos() method. Now tab sequence is proper. THE PROBLEM IS Through the arrow keys, I could not navigate to the last button for which I called SetWindowPos. where as through tab keys I could go to the last control. I will appreciate ur help in this. DETAILED DESCRIPTION I have three buttons (OK,Cancel, Help)on the property sheet. I added a button "Default" to the property sheet and positioned between Cancel and Help. I set the tab sequence as ...

SharePoint Alternate Access Mapping to my Dynamics GP BP site
Hello, If I have posted this question in the wrong forum please let me know! I’m looking for some guidance on changing the Alternate Access Mapping for my Dynamics Business Portal site. So I can implement Single Sign On via ISA Server which is supported on one web listener. Present configuration: Default Zone: http://geneva:222 I would like to add: Internet Zone: http://dynamicsbp.proclub.com I presently have another SharePoint Collection (The Main Portal) http://geneva configured which listens on Port: 80. I tried to add the Alternate Access Mapping http://dynamicsbp.proclub.com...

dynamic charting #2
Hi I have to update a weekly spreadsheet With week numbers in column B x series and three y series percentages in G,H,I columns. Could someone please help I need these to update automatically. If someone can explain in detail I would appreciate 39 2097 2165 68 1770.7 97 96.86 118.43 40 2443.15 2201 -242.15 1548.93 97 111.00 157.73 41 2162.83 2259 96.17 1600.78 97 95.74 135.11 42 2163.08 2241 77.92 1545.1 97 96.52 140.00 43 2137.5 2271 133.5 1664.6 97 94.12 128.41 44 2160.67 2298 137.33 1455 97 94.02 148.50 Ta Brian ...

Default Sort
How do I program Excel to Sort everything by the data in the 5th column automatically, by default Is that possible? or do I have to go in and sort it every time myself? Hi you may use the worksheet_change event to check if new data has been entered but I would recommend to sort manually -- Regards Frank Kabel Frankfurt, Germany Uly wrote: > How do I program Excel to Sort everything by the data in the 5th > column automatically, by default ? Is that possible? or do I have to > go in and sort it every time myself? Thank you Frank for responding! I will continue to do it manual...

Sort Macro 03-24-10
Can I have a Macro that will automatically filter a spread sheet, pick a specific value from a different sheet in the work book and copy the results to a different sheet in the workbook? For example: Sheet 1 has the list of material numbers that are being ran. Sheet 2 has the list of orders for that day with specific material numbers. Sheets 3 - 9 is where the data will be coppied to for each individual line. A separate sheet has been made for each line. I will need to use the data from sheet two 7 different times to gather all the data for each line/material. ...

I can't open Office Data File from CDs #2
Dear technical experts, I have been facing a security problem with Microsoft Outlook files. I exported Outlook items into Office outlook files (with the extension is .pst). The exported files were firstly saved on my computer (different path and location in comparison with default path of Office Outlook files in Documents and Settings). I can open my exported files easily. Because the size of outlook items is very large, I would like to save Office outlook files on another storage device in stead of using hard drives of my computer. I made copy these files to a flash drive (USB dri...

How can I Show data as a % of Sub Group in a Pivot Table
I want to show a column of data (% of City) as a % of a sub group total. Here's my example of how the pivot table should look: State City Carrier Sq Yards % of City (Sq Yards2) Alabama Birmingham Carrier 1 10 20% Carrier 2 20 40% Carrier 3 10 20% Carrier 4 10 20% Birmingham Total 50 100% Mobile Carrier 1 10 25% Carrier 2 10 25% Carrier 3 1...

Sorting tables on protected worksheets
How do we configure our worksheet so that we can turn protection ON, but still enable the table filter buttons in order to sort columns in a table? No matter what settings we select for protection, including the sort and auto-filter options, we cannot sort tables on a protected sheet. What is the secret to allowing table sorts on a protected sheet? Generally one has code to unprotect, code to do the sort, then re-protect. By "tables" do you a real Table created by Insert>Table? I can provide code that turns off protection when you click inside a Table. Clic...

Assign value based on sort
Hi Groupies! I am in the final stages of a database for a golf tournament and I would like to automate the flighting, hole assignment and tee time for the final round. I have a query (qrySatFlights) that calculates a total score based on the first two rounds of golf, the field is called SubTotal. Based on the value in SubTotal, I would like Access to assign the correct values in the Flights field, the SatHole field and the SatTeeTime field. Here is what needs to happen: For the first 9 flights there are 16 golfers. The 16 players with the lowest SubTotal go in the Championship Flight...

Sorting protected worksheet
Is there any way to sort a protected worksheet? I looked thru the posts here and found where someone said to create a macro that unprotected the sheet, then sorted the data, then protected it again. I tried that, but everytime I try to create the macro I get this error "Project Locked - Project is unviewable". Any suggestions? If the workbook is shared, you won't be able to unprotect it. Perhaps that's the problem. Phyllis wrote: > Is there any way to sort a protected worksheet? I looked > thru the posts here and found where someone said to create > a m...

how can i increase number of rows beyone 65536 in excel sheet
the maximum of number of rows that are available in an excel sheet are only 65536,i would like to enhance it to my choice. Plase mail me how can i do it ,either by writing a macro , or other way. please also mail me the entire procedure, where to correct and where to run the macro etc. You want the car washed and the dog taken out as well? There is no way of extending this, use a database or multiple sheets -- Regards, Peo Sjoblom "increasing number of rows in excel sheet" <increasing number of rows in excel sheet@discussions.microsoft.com> wrote in message news:5F6...

Need a method to Dynamicly change the X axis Scale on a chart. #2
I dynamically change the amount of data on a chart. This causes the X axis Scale to either to either show too few values or too many values ( a solid black line if too many). I would like a method to dynamically change the scale values in relation to the data span. Joe - Do you want to limit the number of points displayed? http://peltiertech.com/Excel/Charts/Dynamics.html http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246 Or do you want to link the axis scales to something you calculate in the sheet? http://peltiertech.com/Excel/Zips/SwitchXYAxes.zip - Jon ----...

Dynamically populate a drop down list
Hi, I'm trying to dynamically populate a drop down list in MS CRM (1.2) based on certain parameters. I suppose I could do it using a SQL stored procedure, but I would prefer to use standard/supported CRM customizations. Any assistance in this would be greatly appreciated. Thanks! -Dave- Dave, You can do it in 3.0 pretty easily based upon an example in the SDK... Here are some pretty pictures... http://blogs.msdn.com/midatlanticcrm/archive/2005/12/04/499868.aspx I have not tried it with 1.2, but it could probably work... Thanks Ben "Dave" <renor321@yahoo.com> wrot...

CRM Integration with MBS/Dynamics products
Hi, Anyone know anything about the integration between CRM 3.0 and ...say.... Dynamix AX/Axapta? I'm thinking sharing of customer data and such.... Jeppe Jespersen Denmark There is nothing available yet. You would need to build it. -- Matt Parks MVP - Microsoft CRM "Jeppe Jespersen" <jdj curly jdj dot dk> wrote in message news:OAfVImWLGHA.3276@TK2MSFTNGP09.phx.gbl... Hi, Anyone know anything about the integration between CRM 3.0 and ...say.... Dynamix AX/Axapta? I'm thinking sharing of customer data and such.... Jeppe Jespersen Denmark ...

pointing to a specific row
I have a from which is pulling in a column (named Loc_Desc) from a table named "Input". What I am wondering is, how can I set it up so that it is looking at a specific ROW in that column, instead of looking at the first row each time I load the form? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200705/1 ...

Outlook is throwing away my incoming mail not sorting right
Lately, Outlook has gone crazy! It's trashing my incoming mail and sorting all wrong! I know how to set up the auto sort (making sure they go to correct folders etc). It was working right at first and one day just went nuts. I keep having to recreate the sorts. What's happening? ...

Copy cells with data and paste
Anyone, I am trying to do what I hope is a simple task. I need to have a two part macro that will do the following. 1. Copy only the cells in a certain range that have data in the cells. 2. I then need the macro to paste this data into a new worksheet on this first available blank row thanks in advance for anyones help on this. Bill To learn, Record a macro while use data>filter>autofilter to filter the NON blanks>copy>paste> Then clean up the macro To determine the last row use lastrow=cells(rows.count,"a").end(xlup).row+1 Post back with further questions. -- ...

Dynamics GP 10 and Dynamics AX 4.0
just a half year ago, our company has started to work with Dynamics AX 4.0. Now i am doing some programming in X++ and almost customizing reports. Accidently, i stumpled on Dynamics GP 10.0 and i am wondering what s the difference between AX and GP? What stands GP for? Maybe GP is to replace AX, and our company backs to wrong horse? Does GP 10 contains AX 4.0? I have learnt lot of X++ programming, would it be possible to using X++ in the GT 10 field, too? Great Plains (GP) is another ERP system that Microsoft acquired a few years ago when it acquired the company of the same name. GP ...