Need to Sort Multilevel Data List

[NickH]

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

 

[Ron Coderre]

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

 

[Tom Ogilvy]

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.

 

[NickH]

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.

 

[NickH]

Doh!

Please note that, in each case, the 'Etc.' has wrapped off the end of
the preceding line in my dummy table

NickH

 

[Tom Ogilvy]

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

(e-mail address removed)

 

[NickH]

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

 

[NickH]

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#1547d34052d5f6f0

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