Finding Count of Groups of Data in a Row

[wmunsey81]

Hello all my Excel Gurus!

I want to count the number of segments, or groups of data, in this array. The results should be 3. (I think I might have to add zeros in the blanks)

[TABLE="width: 907"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]20[/TD]
[TD][/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]65[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]22[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Then add one more step into this.
If I had 20 in stock. How many segments are greater than 20. The answer would be 2.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]20[/TD]
[TD][/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]65[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]22[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The groups would be events as each column is basically a day of use of an item. I have a feeling that the second answer is a nesting of the first answer. If anyone could help or needs more info (it took me a little bit to understand the question as well), let me know.

 

[Fennek]

Hi,

just a hint. Use something like this

for each Ar in rows(1).specialcells(2).areas
'here the code, e.g. S = sum(Ar) 
next Ar

regards

(I have no plan to write the code)

 

[MickG]

Perhaps this:-

[COLOR="Navy"]Sub[/COLOR] MG14Dec43
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Range("1:1").SpecialCells(xlCellTypeConstants).Areas
    [COLOR="Navy"]If[/COLOR] Application.Max(R) > 20 [COLOR="Navy"]Then[/COLOR] c = c + 1
[COLOR="Navy"]Next[/COLOR] R
MsgBox "Groups above 20 = " & c
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Marcelo Branco]

Hi Mick

I'm not sure, but i think this line of code

If Application.Max(R)> 20 Then c = c + 1
should be
If Application.Sum(R)> 20 Then c = c + 1

A 10-10-10 group will not be counted by your code, while, *i suppose*, because the sum of its elements is greater than 20 it must be counted.

Let's wait for what OP says.

M.

 

[MickG]

Hi, Marcelo
I was thinking any single number in Group > 20, but your probably right.
Mick

 

[Marcelo Branco]

Hi, Marcelo
I was thinking any single number in Group > 20, but your probably right.
Mick

I was trying to create a formula (tough), but when i saw your code - so simple and efficient - i gave up
By the way, i think would be better convert it to a UDF.

M.

 

[Marcelo Branco]

oops..not sure the SpecialCells method works in UDFs

M.

 

[MickG]

I think your right there !!!
Perhaps another UDF option:-

Function Plus20(rng [COLOR="Navy"]As[/COLOR] Range) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Application.Volatile
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] rng
    [COLOR="Navy"]If[/COLOR] Not IsEmpty(R) [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = R Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, R)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] nRng.Areas
    If Application.Max(R) > 20 Then c = c + 1 '[COLOR="Green"][B] Change to sum as required !!![/B][/COLOR]
[COLOR="Navy"]Next[/COLOR] R
Plus20 = IIf(c > 0, "Groups above 20 = " & c, "No Groups above 20")
[COLOR="Navy"]End[/COLOR] Function

Regards Mick

 

[Marcelo Branco]

Another

Function CountGroup(r As Range, dblCrit As Double)
    Dim rCell As Range, dblTot As Double
    
    For Each rCell In r
        If rCell > 0 Then
            dblTot = dblTot + rCell.Value
        Else
            If dblTot > dblCrit Then CountGroup = CountGroup + 1
            dblTot = 0
        End If
    Next rCell
    If dblTot > dblCrit Then CountGroup = CountGroup + 1
End Function

M.

 

[wmunsey81]

So is there a function answer for this? For example, have the result in column 11. The reason being is that there is probably 50 rows with 31 columns and needing a result for each row.