Simple problem complex solution?

[Aaron]

Hi,

I have had this problem for quite a while and I couldnt get it
resolved on here before but I am hoping someone might be able to sort
it for me.

Here is the situation:

I have a column with 10 answer boxes in it.
Each box displays an "error" in the form of a number.
The boxes can have the same error or different errors or no errors.
The errors are typically anything from 0.00001 to 1.

I have a series of 5 more result boxes that I need to group the errors
in their most logical groupings into.

eg series 1

0
0.001
0
0.001
0
0
0
0.002
0.001
0

As a human looking at the numbers it is easy to group them logically
as:

First 4 numbers together, next 3 together, last 3 on their own each.
(I dont have to populate all 5 result boxes)

eg series 2

0
0
0
0
0
0
0
0.001
0.001
0

As a human looking at the numbers it is easy to group them logically
as:

First 7 numbers together, next 2 together, last 1 on its own. (I dont
have to populate all 5 result boxes)

Can this be done using code or is it better through complex nested if
statements to decide the groupings?

Thanks in advance.

Aaron.

 

[Ron Rosenfeld]

Hi,

I have had this problem for quite a while and I couldnt get it
resolved on here before but I am hoping someone might be able to sort
it for me.

Here is the situation:

I have a column with 10 answer boxes in it.
Each box displays an "error" in the form of a number.
The boxes can have the same error or different errors or no errors.
The errors are typically anything from 0.00001 to 1.

I have a series of 5 more result boxes that I need to group the errors
in their most logical groupings into.

eg series 1

0
0.001
0
0.001
0
0
0
0.002
0.001
0

As a human looking at the numbers it is easy to group them logically
as:

First 4 numbers together, next 3 together, last 3 on their own each.
(I dont have to populate all 5 result boxes)

eg series 2

0
0
0
0
0
0
0
0.001
0.001
0

As a human looking at the numbers it is easy to group them logically
as:

First 7 numbers together, next 2 together, last 1 on its own. (I dont
have to populate all 5 result boxes)

Can this be done using code or is it better through complex nested if
statements to decide the groupings?

Thanks in advance.

Aaron.

In order to do this through code, you will need to set out the rules that you
are using to decide on your groupings.

For example, in your first set:

--------------------------
0
0.001
0
0.001
0
0
0
0.002
0.001
0

 

[Aaron]

In order to do this through code, you will need to set out the rules thatyou
are using to decide on your groupings.

For example, in your first set:

--------------------------
0
0.001
0
0.001
0
0
0
0.002
0.001
0
----------------------------

there are other groupings than the one you proposed that could be seen as
"logical groupings", depending on the logical rules you are using.
--ron

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.

 

[Ron Rosenfeld]

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