Counting groups

[paulopinto]

I need to count the occurrences of groups of symbols in a column as follows:


[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl68, width: 256, colspan: 4"]Symbols[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl66"]X[/TD]
[TD="class: xl66"]O[/TD]
[TD="class: xl66"]O[/TD]
[TD="class: xl66"]X[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl66"]X[/TD]
[TD="class: xl66"]X[/TD]
[TD="class: xl66"]O[/TD]
[TD="class: xl66"]X[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl66"]O[/TD]
[TD="class: xl66"]X[/TD]
[TD="class: xl66"]X[/TD]
[TD="class: xl66"]X[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl66"]X[/TD]
[TD="class: xl66"]O[/TD]
[TD="class: xl66"]X[/TD]
[TD="class: xl66"]X[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl66"]X[/TD]
[TD="class: xl66"]X[/TD]
[TD="class: xl66"]X[/TD]
[TD="class: xl66"]O[/TD]
[/TR]
[TR]
[TD="class: xl65"]Groups[/TD]
[TD="class: xl79, colspan: 4"]Occurrences[/TD]
[/TR]
[TR]
[TD="class: xl67"]O[/TD]
[TD="class: xl71"]1[/TD]
[TD="class: xl72"]2[/TD]
[TD="class: xl72"]0[/TD]
[TD="class: xl73"]1[/TD]
[/TR]
[TR]
[TD="class: xl69"]OO[/TD]
[TD="class: xl74"]0[/TD]
[TD="class: xl68"]0[/TD]
[TD="class: xl68"]1[/TD]
[TD="class: xl75"]0[/TD]
[/TR]
[TR]
[TD="class: xl69"]OOO[/TD]
[TD="class: xl74"]0[/TD]
[TD="class: xl68"]0[/TD]
[TD="class: xl68"]0[/TD]
[TD="class: xl75"]0[/TD]
[/TR]
[TR]
[TD="class: xl70"]OOOO[/TD]
[TD="class: xl76"]0[/TD]
[TD="class: xl77"]0[/TD]
[TD="class: xl77"]0[/TD]
[TD="class: xl78"]0[/TD]
[/TR]
[TR]
[TD="class: xl67"]X[/TD]
[TD="class: xl71"]0[/TD]
[TD="class: xl72"]1[/TD]
[TD="class: xl72"]0[/TD]
[TD="class: xl73"]0[/TD]
[/TR]
[TR]
[TD="class: xl69"]XX[/TD]
[TD="class: xl74"]2[/TD]
[TD="class: xl68"]1[/TD]
[TD="class: xl68"]0[/TD]
[TD="class: xl75"]0[/TD]
[/TR]
[TR]
[TD="class: xl69"]XXX[/TD]
[TD="class: xl74"]0[/TD]
[TD="class: xl68"]0[/TD]
[TD="class: xl68"]1[/TD]
[TD="class: xl75"]0[/TD]
[/TR]
[TR]
[TD="class: xl70"]XXXX[/TD]
[TD="class: xl76"]0[/TD]
[TD="class: xl77"]0[/TD]
[TD="class: xl77"]0[/TD]
[TD="class: xl78"]1[/TD]
[/TR]
</tbody>[/TABLE]

Please, how can I do that?
Thanks.

 

[MickG]

Try this:-
Symbols start "A2"
Groups symbols start "G2".
Results start "H2"

[COLOR=navy]Sub[/COLOR] MG21Sep08
[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] ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Rng1 [COLOR=navy]As[/COLOR] Range, Temp [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Sp [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]Set[/COLOR] Rng1 = Range("G2", Range("G" & Rows.Count).End(xlUp))
Rng1.Offset(, 1).Resize(, 4).Value = 0

[COLOR=navy]For[/COLOR] ac = 0 To 3
  nStr = ""
  [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng.Offset(, ac)
        [COLOR=navy]If[/COLOR] Dn.Address = Rng.Offset(, ac)(1).Address [COLOR=navy]Then[/COLOR]
            nStr = Dn
       [COLOR=navy]Else[/COLOR]
            nStr = nStr & IIf(Dn.Value = Temp, "", ",") & Dn.Value
        [COLOR=navy]End[/COLOR] If
            Temp = Dn.Value
    [COLOR=navy]Next[/COLOR] Dn
        Sp = Split(nStr, ",")
        [COLOR=navy]For[/COLOR] n = 0 To UBound(Sp)
            [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng1
                [COLOR=navy]If[/COLOR] Dn = Sp(n) [COLOR=navy]Then[/COLOR]
                    Dn.Offset(, ac + 1) = Dn.Offset(, ac + 1) + 1
                    [COLOR=navy]Exit[/COLOR] For
                [COLOR=navy]End[/COLOR] If
            [COLOR=navy]Next[/COLOR] Dn
        [COLOR=navy]Next[/COLOR] n
[COLOR=navy]Next[/COLOR] ac
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick