VBA: Sorting arrays in hierarchichal data structure: Help!!!

[Karen Lee]

Help! i have spent many hours poring over this so i hope somebody out
there has some ideas:

I have some data which kind of looks like the following in it's
simplest form:

LEVEL ID INSTRUMENT DEVIATION
1 0 Entry Structure 100
2 0 Beverages 20
3 1 Allied Domecq 8
3 2 Diageo 2
3 3 Scottish Newcastle 10
2 0 Mining 30
3 4 GOC 10
3 5 Rio Tinto 20

The data is stored in an array let's say in this example: varData(8,
4)
(8 rows, 4 columns)

I am trying to develop an algorithm to sort the data by DEVIATION in
hierarchical order - that is, sort everything at level 1 first, then
2, then 3.

Once sorted the data will look like this:

1 0 Entry Structure 100
2 0 Mining 30
3 5 Rio Tinto 20
3 4 GOC 10
2 0 Beverages 20
3 3 Scottish Newcastle 10
3 1 Allied Domecq 8
3 2 Diageo 2

It looks simple enough but once you start working on it you end up
going around in circles and then get confused/frustrated/give up/go
mad. I'm working with much more levels than this and it's not nice.

Anyone help? I'm slowly going insane............

[Tom Ogilvy]

If I sort on Deviation within Level, then all the levels should be together
like this:

LEVEL ID INSTRUMENTATION DEVIATION DUM
1 0 Entry Structure 100 1
2 0 Mining 30 6
2 0 Beverages 20 2
3 5 Rio Tinto 20 8
3 3 Scottish Newcastle 10 5
3 4 GOC 10 7

3 1 Allied Domecq 8 3

3 2 Diageo 2 4

but you don't show that as your result

You have level 2 Beverages mixed in with Leve 3. Could you state a little
more clearly what you want to do.

--
Regards,
Tom Ogilvy

"Karen Lee" <karen...@hsbcam.com> wrote in message
news:77893d86.03091...@posting.google.com...

[Karen]

We are not sorting solely by deviation. Rows are related like a 'tree'
structure and level hierarchy from the original data must be maintained.

It might help if I show the data like this:

1 0 Entry Structure 100

2 0 Beverages 20
3 1 Allied Domecq 8
3 2 Diageo 2
3 3 Scottish Newcastle 10
2 0 Mining 30
3 4 GOC 10
3 5 Rio Tinto 20

- Level 1 acts as the root and is the umbrella for everything - it is
always at the top.

- At level 2 Beverages and Mining act as group headers for everything
underneath (which are the level 3s). So instruments 1, 2 and 3 must
always be under Beverages and instruments 4 and 5 under Mining.

- The level 2s are sorted first, including everything underneath:

1 0 Entry Structure 100
2 0 Mining 30

3 4 GOC 10
3 5 Rio Tinto 20

2 0 Beverages 20
3 1 Allied Domecq 8
3 2 Diageo 2
3 3 Scottish Newcastle 10

- Then we sort the level 3 instruments to give the finished product:

1 0 Entry Structure 100
2 0 Mining 30
3 5 Rio Tinto 20
3 4 GOC 10
2 0 Beverages 20
3 3 Scottish Newcastle 10
3 1 Allied Domecq 8
3 2 Diageo 2

Rgds,

K

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Karen Lee]

Is probably a bit more clearer if data is shown like this:

Original structure:

1 0 Entry Structure 100
2 0 Beverages 20
3 1 Allied Domecq 8
3 2 Diageo 2
3 3 Scottish Newcastle 10
2 0 Mining 30
3 4 GOC 10
3 5 Rio Tinto 20

After Sorting:

1 0 Entry Structure 100
2 0 Mining 30
3 5 Rio Tinto 20
3 4 GOC 10
2 0 Beverages 20
3 3 Scottish Newcastle 10
3 1 Allied Domecq 8
3 2 Diageo 2

Order of data is: Level No, Instrument ID, Name, Deviation

Note that Group Headers have ID of zero.

I'm looking for an algorithm which can perform the sorting with the
least hassle and best performance, using either arrays or collections.

Rgds,

K

karen...@hsbcam.com (Karen Lee) wrote in message news:<77893d86.03091...@posting.google.com>...

[Peter Huang [MSFT]]

Hi Karen,

I set the minimumized weight of a item at the begin. Every comparison
between two item will raise one item's weigh over another by one.
Also, if the item has subitem, all its subitems will be raise corresponding
weigh.

LEVEL ID INSTRUMENT DEVIATION
1 0 Entry Structure 100 7 7
2 0 Beverages 20 3 3 3
3 1 Allied Domecq 8 0 1 1 1
3 2 Diageo 2 0 0 0
3 3 Scottish Newcastle 10 0 2 2
2 0 Mining 30 2 6 6
3 4 GOC 10 0 0 4 4
3 5 Rio Tinto 20 0 1 5 5

Did this works work for you?

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------
>From: karen...@hsbcam.com (Karen Lee)
>Newsgroups: microsoft.public.excel.programming
>Subject: Re: VBA: Sorting arrays in hierarchichal data structure: Help!!!
>Date: 12 Sep 2003 02:17:42 -0700
>Organization: http://groups.google.com/
>Lines: 81
>Message-ID: <77893d86.0309...@posting.google.com>
>References: <77893d86.03091...@posting.google.com>
>NNTP-Posting-Host: 193.108.78.10
>Content-Type: text/plain; charset=ISO-8859-1
>Content-Transfer-Encoding: 8bit
>X-Trace: posting.google.com 1063358263 24166 127.0.0.1 (12 Sep 2003
09:17:43 GMT)
>X-Complaints-To: groups...@google.com
>NNTP-Posting-Date: 12 Sep 2003 09:17:43 GMT
>Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!newsfee
d01.sul.t-online.de!t-online.de!newspeer1-gui.server.ntli.net!ntli.net!sn-xi
t-02!sn-xit-04!sn-xit-01!sn-xit-09!supernews.com!postnews1.google.com!not-fo
r-mail
>Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.programming:415240
>X-Tomcat-NG: microsoft.public.excel.programming

[Karen]

Peter,

I'm having difficulty visualising how you did this, even
with the numbers in front of me. Please kindly explain
using a small example.

Thanks,

Karen

>.
>

[Peter Huang [MSFT]]

Hi Karen,

Here is my sample.

LEVEL ID INSTRUMENT DEVIATION
1 0 Entry Structure 100 7

2 0 Beverages 20 3

3 1 Allied Domecq 8 1

3 2 Diageo 2 0

3 3 Scottish Newcastle 10 2

2 0 Mining 30 6
3 4 GOC 10 4
3 5 Rio Tinto 20 5

Sub sort()

'initialize
j = 2
highlevel = 1
lowlevel = 3
While j < 10
i = j
level = Sheet1.Cells(i, 1)
'Sheet1.Cells(10 + level, 1) = 1
i = i + 1
Sheet1.Cells(j, 5) = 0
While Sheet1.Cells(i, 1) <> "" And Sheet1.Cells(i, 1) > level
Sheet1.Cells(j, 5) = Sheet1.Cells(j, 5) + 1
i = i + 1
Wend
j = j + 1
Wend

'sort the level 3
j = 2
While j < 10
If Sheet1.Cells(j, 1) = lowlevel - 1 Then
itcn = Sheet1.Cells(j, 5)
For i = 1 To itcn - 1
For z = i + 1 To itcn
If Sheet1.Cells(j + i, 4) >= Sheet1.Cells(j + z, 4) Then
Sheet1.Cells(j + i, 5) = Sheet1.Cells(j + i, 5) + 1
Else
Sheet1.Cells(j + z, 5) = Sheet1.Cells(j + z, 5) + 1
End If
Next
Next
End If
j = j + 1
Wend
'sort the level 2
'set the base count
For j = 2 To 10
If Sheet1.Cells(j, 1) = lowlevel - 1 Then
Sheet2.Cells(j, 5) = Sheet1.Cells(j, 5)
End If
Next

'begin to sort the level 2
For j = 2 To 10
If Sheet1.Cells(j, 1) = lowlevel - 1 Then
For i = j + 1 To 10
If Sheet1.Cells(i, 1) = lowlevel - 1 Then
Sheet2.Cells(j, 5) = Sheet2.Cells(j, 5)
If Sheet1.Cells(j, 4) >= Sheet1.Cells(i, 4) Then
Sheet1.Cells(j, 5) = Sheet1.Cells(j, 5) + 1 +
Sheet2.Cells(i, 5)
For z = j + 1 To j + Sheet2.Cells(j, 5)
Sheet1.Cells(z, 5) = Sheet1.Cells(z, 5) + 1 +
Sheet2.Cells(i, 5)
Next
Else
Sheet1.Cells(i, 5) = Sheet1.Cells(i, 5) + 1 +
Sheet2.Cells(j, 5)
For z = i + 1 To i + Sheet2.Cells(i, 5)
Sheet1.Cells(z, 5) = Sheet1.Cells(z, 5) + 1 +
Sheet2.Cells(j, 5)
Next
End If
End If
Next
End If
Next
End Sub

My idea is to compare one item with all the other items in the same
category,e.g. all the level 3 under the same level 2 will be a category.
Each comparison will raise the weight of the winner item.
e.g. A is box with 4 balls high, B is a box with 3 balls high.
If A is placed higher than B , then The lowest ball in the A will be 4
balls high.

Did I answer your question?

Regards,
Peter Huang
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
>Content-Class: urn:content-classes:message
>From: "Karen" <karen...@hsbcam.com>
>Sender: "Karen" <karen...@hsbcam.com>
>References: <77893d86.03091...@posting.google.com>
<77893d86.0309...@posting.google.com>
<hhbTxST...@cpmsftngxa06.phx.gbl>

>Subject: Re: VBA: Sorting arrays in hierarchichal data structure: Help!!!

>Date: Fri, 12 Sep 2003 08:36:06 -0700
>Lines: 181
>Message-ID: <013a01c37943$94f94640$a101...@phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcN5Q5T59VVALtRuT7iCa3auWFTzaA==
>Newsgroups: microsoft.public.excel.programming
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.programming:415350
>NNTP-Posting-Host: TK2MSFTNGXA09 10.40.1.161
>X-Tomcat-NG: microsoft.public.excel.programming

[Karen Lee]

I get your drift. Very helpful, thanks a lot!

Regards,

Karen

v-ph...@online.microsoft.com (Peter Huang [MSFT]) wrote in message news:<zHGiBk4e...@cpmsftngxa07.phx.gbl>...