Counting the groups

[Nykyta]

I need to solve the nnext issue (in worksheet or VBA - never mind). In the next column, I have something as:


group

group

blank

group

group

group

blank

group

group

blank

group

group

group

group

blank

group

blank

group

blank

group

blank

group

group

group

blank


... etc ...
What is the best way to find how many groups I have there?
a) groups with 1 "group" repeating;

b) groups with 2 "group" repeating one after another;

c) groups with 3 "group" repeating one after another;

d) groups with 4 "group" repeating one after another;

e) groups with 5 "group" repeating one after another; etc.

I hope that my question is clear enoug. Thanks in advance! Nyk

 

[MARK858]

You can try the code below.

Important Please note it assumes

The Groups are in column A
You have no column headers.
You have nothing in columns C,E & F
That the Groups (column A) are constants i.e. not the results of formula

Sub CountGrp()
    Dim c As Range, ar As Range
    For Each ar In Columns(1).SpecialCells(2).Areas
        ar.Cells(1).Offset(, 2) = ar.Rows.Count
    Next
    
    Rows(1).Insert
    Cells(1, 3).Value = "Group count"
    Columns("C:C").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Columns _
                                                                       ("C:C"), CopyToRange:=Range("E1"), Unique:=True
    Columns("E:E").SpecialCells(4).Delete
    For Each c In Range("F2:F" & Range("E" & Rows.Count).End(xlUp).Row)
        c = WorksheetFunction.CountIf(Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row), c.Offset(, -1))
    Next
     Cells(1, 5).Value = "Items"
     Cells(1, 6).Value = "Item count"
     Range("C:C,E:F").EntireColumn.AutoFit

End Sub

 

[MickG]

Try this:-
Results start "C1"

[COLOR="Navy"]Sub[/COLOR] MG16Aug39
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A:A").SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
    [COLOR="Navy"]If[/COLOR] Not .Exists("Grp of " & Dn.Count) [COLOR="Navy"]Then[/COLOR]
        .Add "Grp of " & Dn.Count, 1
    [COLOR="Navy"]Else[/COLOR]
        .Item("Grp of " & Dn.Count) = .Item("Grp of " & Dn.Count) + 1
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Range("C1").Resize(.Count, 2) = Application.Transpose(Array(.Keys, .items))
[COLOR="Navy"]End[/COLOR] With


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Oeldere]

NON VBA solution.

Book1
	A	B
1
2	group	1
3	group	1
4
5	group	2
6	group	2
7	group	2
8
9	group	3
10	group	3
11
12	group	4
13	group	4
14	group	4
15	group	4
16
17	group	5
18
19	group	6
20
21	group	7
22
23	group	8
24	group	8
25	group	8
Blad9

Cell Formulas
Range	Formula
B1	=IF(A1="","",COUNTIF($A$1:$A1,""))
B2	=IF(A2="","",COUNTIF($A$1:$A2,""))
B3	=IF(A3="","",COUNTIF($A$1:$A3,""))
B4	=IF(A4="","",COUNTIF($A$1:$A4,""))
B5	=IF(A5="","",COUNTIF($A$1:$A5,""))
B6	=IF(A6="","",COUNTIF($A$1:$A6,""))
B7	=IF(A7="","",COUNTIF($A$1:$A7,""))
B8	=IF(A8="","",COUNTIF($A$1:$A8,""))
B9	=IF(A9="","",COUNTIF($A$1:$A9,""))
B10	=IF(A10="","",COUNTIF($A$1:$A10,""))
B11	=IF(A11="","",COUNTIF($A$1:$A11,""))
B12	=IF(A12="","",COUNTIF($A$1:$A12,""))
B13	=IF(A13="","",COUNTIF($A$1:$A13,""))
B14	=IF(A14="","",COUNTIF($A$1:$A14,""))
B15	=IF(A15="","",COUNTIF($A$1:$A15,""))
B16	=IF(A16="","",COUNTIF($A$1:$A16,""))
B17	=IF(A17="","",COUNTIF($A$1:$A17,""))
B18	=IF(A18="","",COUNTIF($A$1:$A18,""))
B19	=IF(A19="","",COUNTIF($A$1:$A19,""))
B20	=IF(A20="","",COUNTIF($A$1:$A20,""))
B21	=IF(A21="","",COUNTIF($A$1:$A21,""))
B22	=IF(A22="","",COUNTIF($A$1:$A22,""))
B23	=IF(A23="","",COUNTIF($A$1:$A23,""))
B24	=IF(A24="","",COUNTIF($A$1:$A24,""))
B25	=IF(A25="","",COUNTIF($A$1:$A25,""))

 

[Aladin Akyurek]

Using a formula-based approach...

Row\Col	A​	B​	C​	D​
1​			4​
2​	group		Size	Freq
3​	group		1​	3​
4​			2​	2​
5​	group		3​	2​
6​	group		4​	1​
7​	group
8​
9​	group
10​	group
11​
12​	group
13​	group
14​	group
15​	group
16​
17​	group
18​
19​	group
20​
21​	group
22​
23​	group
24​	group
25​	group

C1, control+shift+enter, not just enter:

=MAX(FREQUENCY(IF($A$2:$A$25="group",ROW($A$2:$A$25)),
    IF(1-($A$2:$A$25="group"),ROW($A$2:$A$25))))

C3, just enter and copy down:

=IF(ROWS($C$3:C3)<=$C$1,ROWS($C$3:C3),"")

D3, control+shift+enter, not just enter, and copy down:

=IF($C3="","",SUM(IF(FREQUENCY(IF($A$2:$A$25="group",ROW($A$2:$A$25)),
    IF(1-($A$2:$A$25="group"),ROW($A$2:$A$25)))=$C3,1)))

 

[Oeldere]

In addition with my reply in #4, considering the anwers of Aladin in #5, you can also use this one.

Book1
	D	E
1		Count
2	1	2
3	2	3
4	3	2
5	4	4
6	5	1
7	6	1
8	7	1
9	8	3
Blad9

Cell Formulas
Range	Formula
E2	=COUNTIF($B$2:$B$25,$D2)

 

[Nykyta]

Probably I needed to prepare screenshot and failse-sample first. Apologize to each of you mates. I'll post both now and then, I'll checkout your solutions. Screenshot comment: the data in column C I am enetring manualy. Need to find solution for that. To do it automaticalu. Everything else is solved.

Sample Excel file: http://laban.rs/MrExcel/CO_Sample.xlsx
Screenshot:

 

[MARK858]

the data in column C I am enetring manualy. Need to find solution for that. To do it automaticalu. Everything else is solved.

Sub CountGrp()
    Dim c As Range, ar As Range
    For Each ar In Range("B4:B90").SpecialCells(2).Areas
        ar.Cells(1).Offset(, 1) = ar.Rows.Count
    Next
End Sub

 

[Nykyta]

That's it Mark. Thanks a lot! I new that there were simple solution...

 

[MARK858]

Your welcome and actually you can remove the "c As Range" as it was a leftover from the last code i.e.

Sub CountGrp()
    Dim ar As Range
    For Each ar In Range("B4:B90").SpecialCells(2).Areas
        ar.Cells(1).Offset(, 1) = ar.Rows.Count
    Next
End Sub