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

UseLessFuel

New Member
Joined
Dec 22, 2012
Messages
37
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!):confused:.
 
Last edited:

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

UseLessFuel

New Member
Joined
Dec 22, 2012
Messages
37
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
 
Last edited:

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
With your Data starting in "C1 & B1, try this for results starting "E1 & F1".
Code:
[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

New Member
Joined
Dec 22, 2012
Messages
37
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,190
Messages
5,473,034
Members
406,843
Latest member
David_Welland

This Week's Hot Topics

Top