Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to Sort Multilevel Data List
Sorry this is a bit of a ramble - I just hope it makes sense...
I have a problem which I'm hoping someone can help me think outside the box with. A data list of 17 columns and about 6000 rows is arranged in three levels on a sheet. Data in a level 1 row will start in column B, directly beneath each level1 row there may be one or more level 2 rows, pertaining to the same product, whose data will start in column C and beneath each of these there may be one or more level 3 rows - data beginning in column D. I need to sort this data by, for instance, 'Status' - each row, whatever level, will be at some status of Planning, Accepted, Rejected etc. However, it is crucial that each row remains within its respective level under the associated product (level 1). My first thought was to copy all the level 1 rows to a separate sheet and sort them there. Then copy the level 2 rows out to a separate sheet, sort them and insert them back into the sorted level 1 rows and then the same for level 3. Level1 - fine, did that works a treat, all rows (about 2000) copied and sorted within a second. Level2 - not even halfway through after 5 mins Level3 - didn't bother trying For the interface to be acceptable this needs to happen within a couple of seconds. So, thinking outside the box, I figured why not have the routine, that creates the data list in the first place, put some extra 'level sorting' type info into some hidden columns on the right of the data list. Such that I could then use the built-in sort engine by sorting on 'Status' and then sorting on one or more hidden columns to restore the level groups without losing the Status sort within those groups. And that's where I'm stuck. I'm sure this is the right way to go but cannot for the life of me work out what should go in these extra columns or how many I would need. Any bright sparks out there? NickH |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to Sort Multilevel Data List
How is the data list structured?
Like this? Level_1 Level_2 Level_3 Div_1 (blank) (blank) (blank) Dept_1 (blank) (blank) (blank) Emp_1 (blank) Dept_2 (blank) (blank) (blank) Emp_2 (blank) (blank) Emp_3 (blank) (blank) Emp_4 Div_2 (blank) (blank) (blank) Dept_1 (blank) (blank) (blank) Emp_1 (blank) Dept_2 (blank) (blank) (blank) Emp_2 (blank) (blank) Emp_3 (blank) (blank) Emp_4 Or maybe this? Level_1 Level_2 Level_3 Div_1 Dept_1 Emp_1 (blank) Dept_2 Emp_2 (blank) (blank) Emp_3 (blank) (blank) Emp_4 Div_2 Dept_1 Emp_1 (blank) Dept_2 Emp_2 (blank) (blank) Emp_3 (blank) (blank) Emp_4 or is it something else? *********** Regards, Ron XL2002, WinXP "NickH" wrote: Sorry this is a bit of a ramble - I just hope it makes sense... I have a problem which I'm hoping someone can help me think outside the box with. A data list of 17 columns and about 6000 rows is arranged in three levels on a sheet. Data in a level 1 row will start in column B, directly beneath each level1 row there may be one or more level 2 rows, pertaining to the same product, whose data will start in column C and beneath each of these there may be one or more level 3 rows - data beginning in column D. I need to sort this data by, for instance, 'Status' - each row, whatever level, will be at some status of Planning, Accepted, Rejected etc. However, it is crucial that each row remains within its respective level under the associated product (level 1). My first thought was to copy all the level 1 rows to a separate sheet and sort them there. Then copy the level 2 rows out to a separate sheet, sort them and insert them back into the sorted level 1 rows and then the same for level 3. Level1 - fine, did that works a treat, all rows (about 2000) copied and sorted within a second. Level2 - not even halfway through after 5 mins Level3 - didn't bother trying For the interface to be acceptable this needs to happen within a couple of seconds. So, thinking outside the box, I figured why not have the routine, that creates the data list in the first place, put some extra 'level sorting' type info into some hidden columns on the right of the data list. Such that I could then use the built-in sort engine by sorting on 'Status' and then sorting on one or more hidden columns to restore the level groups without losing the Status sort within those groups. And that's where I'm stuck. I'm sure this is the right way to go but cannot for the life of me work out what should go in these extra columns or how many I would need. Any bright sparks out there? NickH |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to Sort Multilevel Data List
two columns
The level 1 identified (product name or ID I assume) The level numer (1, 2 or 3) then sort on Key1: first dummy column Key2: second dummy column Key3: status column If the status doesn't sort propertly, then a third dummy column that holds a number related to the order in which the status must be sorted and use that for Key 3 this assumes that you don't have Product A: lvl1 lvl2 lvl3 lvl3 lvl2 lvl3 lvl3 lvl3 You described lvl1 lvl2 lvl2 lvl3 lvl3 lvl3 lvl3 lvl3 as I understood it and my suggestion assumes the second. -- Regards, Tom Ogilvy "NickH" wrote: Sorry this is a bit of a ramble - I just hope it makes sense... I have a problem which I'm hoping someone can help me think outside the box with. A data list of 17 columns and about 6000 rows is arranged in three levels on a sheet. Data in a level 1 row will start in column B, directly beneath each level1 row there may be one or more level 2 rows, pertaining to the same product, whose data will start in column C and beneath each of these there may be one or more level 3 rows - data beginning in column D. I need to sort this data by, for instance, 'Status' - each row, whatever level, will be at some status of Planning, Accepted, Rejected etc. However, it is crucial that each row remains within its respective level under the associated product (level 1). My first thought was to copy all the level 1 rows to a separate sheet and sort them there. Then copy the level 2 rows out to a separate sheet, sort them and insert them back into the sorted level 1 rows and then the same for level 3. Level1 - fine, did that works a treat, all rows (about 2000) copied and sorted within a second. Level2 - not even halfway through after 5 mins Level3 - didn't bother trying For the interface to be acceptable this needs to happen within a couple of seconds. So, thinking outside the box, I figured why not have the routine, that creates the data list in the first place, put some extra 'level sorting' type info into some hidden columns on the right of the data list. Such that I could then use the built-in sort engine by sorting on 'Status' and then sorting on one or more hidden columns to restore the level groups without losing the Status sort within those groups. And that's where I'm stuck. I'm sure this is the right way to go but cannot for the life of me work out what should go in these extra columns or how many I would need. Any bright sparks out there? NickH |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to Sort Multilevel Data List
Ron & Tom,
Thanks for your replies. Both of you are correct with your first descriptions - I didn't attempt to mimic a table layout because I've tried it before with horribly misleading results. Anyway here goes... 1.0.0 Description | Blank | Blank | 1.0.0 ID | Status | Priority | Etc. Blank | 1.1.0 Description | Blank | 1.1.0 ID | Status | Priority | Etc. Blank | Blank | 1.1.1 Description | 1.1.1 ID | Status | Priority | Etc. Blank | Blank | 1.1.2 Description | 1.1.2 ID | Status | Priority | Etc. Blank | Blank | 1.1.3 Description | 1.1.3 ID | Status | Priority | Etc. Blank | 1.2.0 Description | Blank | 1.2.0 ID | Status | Priority | Etc. Blank | Blank | 1.2.1 Description | 1.2.1 ID | Status | Priority | Etc. 2.0.0 Description | Blank | Blank | 2.0.0 ID | Status | Priority | Etc. Blank | 2.1.0 Description | Blank | 2.1.0 ID | Status | Priority | Etc. Blank | Blank | 2.1.1 Description | 2.1.1 ID | Status | Priority | Etc. Blank | 2.2.0 Description | Blank | 2.2.0 ID | Status | Priority | Etc. Blank | Blank | 2.2.1 Description | 2.2.1 ID | Status | Priority | Etc. There are more columns of course but I am chiefly interested in sorting by Status or Priority. I've put the hierarchy numbers in for clarity - they don't actually exist but I could create them if I thought it would help (which I don't at this point). My latest idea is this (it appears to work on a small data set)... 1) To the right, add a column that simply contains the row number of each row - this will be purely to allow me to restore the data to its current state, which has hierarchical significance. Let's call it the 'Restore' column. 2) Sort the data by 'Status' (or whatever) 3) Add another column containing the row numbers as the list is now sorted. Let's call it the 'Tag' column 4) Sort the data by the 'Restore' column Now the clever bit - which I'm hoping will process fairly quickly... 5) Add a 'NewSort' column which will contain... For Level1, the 'Tag' number For Level2, the Level1 tag number + 0.00001 * its own tag number For Level3, the Level2 tag number + 0.0000000001 * its own tag number 6) Sort the data by 'NewSort' - Done! Please let me know if you see any holes in my logic or if there's a much simpler way that I'm missing. Best regards, NickH PS. Loads of respect for you Tom - your a great contributor and I've gained a lot of help from solutions that you've provided for others in the past. :) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to Sort Multilevel Data List
Doh!
Please note that, in each case, the 'Etc.' has wrapped off the end of the preceding line in my dummy table NickH |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to Sort Multilevel Data List
NickH
If you want to send me a file I will take a look at it. A file with a before sort sheet and a after sort sheet would be illustrative. i don't think you have clearly stated the relationship between level 1 and Status. If a level will not have a pure status - status will be mixed -then when the sort is performed, is status subordinate to level or do you want to all the status Planning records, then all the status accepted records, then all the status rejected records with the level relationship retained. Level 1.0.0 sub levels could appear in multiple status groups. send to -- Regards, Tom Ogilvy "NickH" wrote: Doh! Please note that, in each case, the 'Etc.' has wrapped off the end of the preceding line in my dummy table NickH |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to Sort Multilevel Data List
Sorry Tom,
Company confidential so can't send - I'll do my best to explain. The data starts out hierarchically arranged which reflects the structure of the source database (Borland Caliber - no I'd never heard of it either). We are talking mobile phone features here - A company will naturally have a range of mobile phones all with a different set of features (predominantly this is about software) that go to make up the unique phone package. However, most features are used by more than one phone. The database I am working with stores the data pertaining to these features. Each level 1 record is a unique Feature Each level 2 record is a Requirement - a thing that is needed to make a specific feature work, hence, in any given sort, it must stay under the level 1 feature that it starts under and must only be sorted in relation to other level 2 requirements (if there are any) pertaining to that same feature Each level 3 record is a Sub-component - generally one of many predefined and tested routines that help to fulfill the requirement that makes up the feature. Again, these level 3 records must stay listed beneath their unique level 2 requirement and only be sorted relative to other level 3 records listed under the same level 2 record. When a sort shifts the position of a level 2 requirement relative to another level 2 requirement its level 3 subcomponents must shift with it. It should be noted that a sub-component may occur several times within the datalist under different requirements and the same goes for requirements (level 2) being used by more than one feature (level 1). Therefore unique IDs cannot be relied on in any kind of solution here. Typically a feature has two or three requirements each of which, in turn, may have a dozen or so sub-components. Status and Priority are just two of a number of column heading titles. Each record, be it Level 1,2 or 3, will have a Status of anything from Accepted to Rejected and a Priority ranging High, Medium, Low. I hope this makes it clearer, I do appreciate you looking at this but understand if it's too complicated to get into without seeing the actual data. I'm reasonably confident that the solution I'm coding now (described previously) will work. its just a case of how fast it'll run and whether or not I can adapt it to a later task which will be a 4 level datalist (am I right in thinking Excel's limit is 17 decimal places?). Br, NickH |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to Sort Multilevel Data List
Update:
Finished coding now - The method described in my second entry above works a treat, as long as the appropriate variables are declared as Double to keep the accuracy as high as possible. Process time is about 4 seconds. I have my doubts about it working for 4 levels though. On investigation Excel's limit is 15 decimal places which theoretically is just enough. However, I suspect inaccuracies, due to the binary storing of numbers, as per the IEEE standard, may affect the sort accuracy of large lists. I know I've answered my own question but here's the code, in case anyone else finds it useful, minus the calling procedure which simply passes the number of the column to be sorted - please beware of wrapping and if you want to know how the functions are getting the last cell/row values go here... http://groups.google.co.uk/group/ microsoft.public.excel.programming/browse_thread/thread/ 9dd9598ea0a06049/1547d34052d5f6f0? lnk=st&q=GetRealLastCell&rnum=40&hl=en#1547d34052d 5f6f0 Option Explicit Public Sub SortBy(ByVal SortCol As Integer) Dim i As Long Dim Level1Col As Integer Dim Level2Col As Integer Dim Level3Col As Integer Dim PrevSortCol As Integer Dim TagCol As Integer Dim NewSortCol As Integer Dim wksTarget As Worksheet Dim rngData As Range Dim LastRow As Long Dim Level1Tag As Double Dim Level2Tag As Double Application.ScreenUpdating = False ''' Initialise Variables Set wksTarget = ActiveSheet Set rngData = Range("B3:" & RealLastCell(wksTarget).Address) LastRow = RealLastRow(wksTarget) Level1Col = 2 Level2Col = 3 Level3Col = 4 PrevSortCol = ColNum(wksTarget, "Previous", 2) TagCol = ColNum(wksTarget, "Tag", 2) NewSortCol = ColNum(wksTarget, "New", 2) ''' Store the current sort order under "Previous" For i = 3 To LastRow wksTarget.Cells(i, PrevSortCol) = i Next i ''' Sort according to user's request rngData.Sort Key1:=Cells(3, SortCol), _ Order1:=xlAscending, _ Header:=xlNo, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ''' Store requested sort order under "Tag" For i = 3 To LastRow wksTarget.Cells(i, TagCol) = i Next i ''' Restore the previous sort order rngData.Sort Key1:=Cells(3, PrevSortCol), _ Order1:=xlAscending, _ Header:=xlNo, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ''' Build new sort column based on levels and tags For i = 3 To LastRow If Cells(i, Level1Col) < "" Then Cells(i, NewSortCol) = Cells(i, TagCol) Level1Tag = Cells(i, NewSortCol) ElseIf Cells(i, Level2Col) < "" Then Cells(i, NewSortCol) = Level1Tag + _ (Cells(i, TagCol) / 10000) Level2Tag = Cells(i, NewSortCol) Else Cells(i, NewSortCol) = Level2Tag + _ (Cells(i, TagCol) / 1000000000) End If Next i ''' Do the final sort by NewSortCol rngData.Sort Key1:=Cells(3, NewSortCol), _ Order1:=xlAscending, _ Header:=xlNo, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Application.ScreenUpdating = True End Sub Br, NickH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to sort the data in drop down list | Excel Discussion (Misc queries) | |||
Data Validation List Sort | Excel Programming | |||
Sort Data in a combobox list | Excel Programming | |||
Sort Data in a combobox list | Excel Programming | |||
Bin and sort a large list of data | Excel Discussion (Misc queries) |