On Wed, 8 Jul 2009 18:25:05 -0700 (PDT), Aaron
wrote:


Hi,

Yes I know that is what makes it so hard to do it with code. It is
interpretational and I know code isnt, it is precise there is no grey
area.

The groupings must be consecutive they cant be one from the first one
from the last one from the middle, the patterns must be grouped
together.

One way would be to group all that are the same, searching for a
pattern that starts at the first number ending with the last number
from top to bottom of list. Just group "likes" together.

In the above example it would make 8 groups. I could then use nested
if's to narrow it to 5 from there I am sure.

Is there a way to do this with code and output as many different
groupings that it finds as per example above:

0
0.001
0
0.001
0
0.002
0.001
0

eg 8 logical groupings.

Cheers,

Aaron.

Sure. When you lay out rules that are understandable, such as "group all that
are the same", then that can be done.

Here is a macro that shows how this step can be accomplished.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro
by name, and <RUN.

============================
Option Explicit
Sub Groupings()
Dim rSrc As Range, c As Range, rDest As Range
Dim i As Long
Set rSrc = Range("A1:A11")
Set rDest = Range("B1")
'clear destination range
'could be up to same size as source range
Range(rDest, rDest.Offset(rSrc.Rows.Count - 1, 0)).Clear
i = 1
For Each c In rSrc
'Check for first entry -- always the first group
If c.Address = rSrc(1, 1).Address Then
rDest(1, 1).Value = c.Value
Else
'Not first entry, so check preceding entry for identicality
If c.Value < c.Offset(-1, 0).Value Then
rDest.Offset(i, 0).Value = c.Value
i = i + 1
End If
End If
Next c
End Sub
===================================
--ron