ExcelBanter - VBA: Sorting arrays in hierarchichal data structu Help!!!

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - VBA: Sorting arrays in hierarchichal data structu Help!!! (https://www.excelbanter.com/excel-programming/276750-vba-sorting-arrays-hierarchichal-data-structure-help.html)

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

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

Sorting arrays in hierarchichal data structu Help!!!

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" wrote in message
om...
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............

Sorting arrays in hierarchichal data structu Help!!!

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!

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

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"
Sender: "Karen"
References:

Subject: VBA: Sorting arrays in hierarchichal data structu Help!!!
Date: Fri, 12 Sep 2003 08:36:06 -0700
Lines: 181
Message-ID:
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

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

-----Original Message-----
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 Lee)
Newsgroups: microsoft.public.excel.programming
Subject: VBA: Sorting arrays in hierarchichal data
structu Help!!!
Date: 12 Sep 2003 02:17:42 -0700
Organization: http://groups.google.com/
Lines: 81
Message-ID:

References:
m
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:
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

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

Original structu

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 Lee) wrote in message
e.com...
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............

.

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