count repeated values and consecutive repeated values in each row

[scml]

hi there

I need to count repeated values and consecutive repeated values in each row

based on the rows above I would get this two columns below (1st column is number of repetitions and 2nd column is number of consecutive repetitions)

0 0
1 1
1 0
1 0
1 1
0 0
2 1
1 1
0 0



for example:

<table border="0" cellpadding="0" cellspacing="0" width="130"><col style="mso-width-source:userset;mso-width-alt:950; width:20pt" span="5" width="26"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:20pt" height="20" align="right" width="26">16</td> <td style="width:20pt" align="right" width="26">7</td> <td style="width:20pt" align="right" width="26">1</td> <td style="width:20pt" align="right" width="26">2</td> <td style="width:20pt" align="right" width="26">3</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="right">9</td> <td align="right">8</td> <td align="right">8</td> <td align="right">3</td> <td align="right">21</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20" align="right">1</td> <td align="right">8</td> <td align="right">1</td> <td align="right">12</td> <td align="right">28</td> </tr> </tbody></table>
1st row: no repetitions, no consecutive repetitions
2nd row: 1 repetition; 1 consecutive repetition
3rd row: 1 repetition; no consecutive repetitions

 

[Ron Coderre]

I'm a bit puzzled by the 7th sequence:
5, 7, 7, 22, 7

Why is 7 counted twice as a repeated number?
I expected it to be counted once as a repeated value
with one instance of consecutive repetitions.

 

[scml]

I'm a bit puzzled by the 7th sequence:
5, 7, 7, 22, 7

Why is 7 counted twice as a repeated number?
I expected it to be counted once as a repeated value
with one instance of consecutive repetitions.

well I'm not counting number of repeated values (you are right in that case it would count as only one) but the number of repetitions and then if a number appears 3 times I can say it repeated twice...

 

[scml]

ok considering number of repeated values (and number of consecutive repeated numbers) and not number of repetitions:
I would get this instead:
0 0
2 2
2 0
2 0
2 2
0 0
3 2
2 2
0 0

I'm a bit puzzled by the 7th sequence:
5, 7, 7, 22, 7

Why is 7 counted twice as a repeated number?
I expected it to be counted once as a repeated value
with one instance of consecutive repetitions.

 

[MickG]

Try this:-
Results start "G1"

[COLOR="Navy"]Sub[/COLOR] MG26Jul52
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] Ac = 0 To 4
            [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Offset(, Ac).Value) [COLOR="Navy"]Then[/COLOR]
                .Add Dn.Offset(, Ac).Value, Array(0, 0)
            [COLOR="Navy"]Else[/COLOR]
                Q = .Item(Dn.Offset(, Ac).Value)
                Q(0) = Q(0) + 1
                [COLOR="Navy"]If[/COLOR] Dn.Offset(, Ac - 1) = Dn.Offset(, Ac) [COLOR="Navy"]Then[/COLOR] Q(1) = Q(1) + 1
                .Item(Dn.Offset(, Ac).Value) = Q
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR]
c = c + 1
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
            [COLOR="Navy"]If[/COLOR] .Item(K)(0) > 0 [COLOR="Navy"]Then[/COLOR]
                Cells(c, 7) = .Item(K)(0): Cells(c, 8) = .Item(K)(1)
                [COLOR="Navy"]Exit[/COLOR] For
            [COLOR="Navy"]Else[/COLOR]
                Cells(c, 7) = 0: Cells(c, 8) = 0
            [COLOR="Navy"]End[/COLOR] If
        
    [COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[sous2817]

*wrong post*