Group, Count and Sum contiguous groups of cells in one column

[UseLessFuel]

Hello.
I have a column of numerical data (Column C) which holds various groups of contiguous numbers separated by (truly) blank cells. The size of each group of contiguous numbers varies from only one, up to 200 or more. I would like to group Column C from low-to-high group size, along with the corresponding timestamp data held in Column B. I would also like to get the Count and Sum of each group of contiguous cells. Hope this is clear enough to go on. I am familiar with pivot tables, and some VBA (usually gained from this site!).

 

[UseLessFuel]

Just to add to above:
I found a very helpful VBA code from Rick Rothstein (not sure how to link back to the thread) which neatly summed each "area" in the column, placing each summation in Column D - thanks Rick.

I would still like to arrange the column from low-to-high "area" size if possible, so I can look at each of the same area size easily. The Count of each area size would be helpful too.

Sub SumGroupsOfNumbers()
Dim Ar As Range
For Each Ar In Columns("C").SpecialCells(xlConstants).Areas
Ar(Ar.Count).Offset(, 1).Value = Application.Sum(Ar)
Next
End Sub

 

[MickG]

With your Data starting in "C1 & B1, try this for results starting "E1 & F1".

[COLOR="Navy"]Sub[/COLOR] MG18Oct48
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, AL [COLOR="Navy"]As[/COLOR] Object, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("C:C").SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]Set[/COLOR] AL = CreateObject("System.Collections.ArrayList")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
    [COLOR="Navy"]If[/COLOR] Not AL.Contains(Dn.Count) [COLOR="Navy"]Then[/COLOR] AL.Add Dn.Count
[COLOR="Navy"]Next[/COLOR] Dn
    AL.Sort
c = 1
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(AL.Toarray)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
        [COLOR="Navy"]If[/COLOR] Dn.Count = AL(n) [COLOR="Navy"]Then[/COLOR]
            Cells(c, "F").Resize(Dn.Count) = Dn.Value
            Cells(c, "E").Resize(Dn.Count) = Dn.Offset(, -1).Value
            Cells(c, "E").Resize(Dn.Count).NumberFormat = "hh: mm: ss "
            c = c + Dn.Count + 1
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[UseLessFuel]

Hi Mick. Only just spotted your reply. Many thanks.

Once I re-formatted the timestamps in Column E (which I didn't give you in original description), your code lists each single entry (surrounded by blank cells) in date/time order, followed by a blank line, then each "area" with two entries (surrounded by blank cells) in date/time order, followed by a blank line etc. etc. This is brilliant.

It is obvious now that having a Count of each area is superfluous since each block of areas has the same Count (1 followed by 2, followed by 3 etc. etc.), so now it is an easy task to Sum each area of interest.
Really appreciate this Mick. All the best, Geoff.

 

[MickG]

You're welcome