Counting rows between same value

[colin5392]

I have a list of numbers, some of which are duplicated throughout the list. What I would like to do is count the rows between the same value. So, for example, if I have a list 3, 6, 9, 15, 3 (down in row format):

3
6
9
15
3

How do I count the rows between the 2 3's ?

 

[mrhstn]

Here is one suggestion

Book1
	A	B	C	D
1	3		Find	#Rows
2	6		3	3
3	9
4	15
5	3
6	7
7	8
8	4
9	6
Sheet2

Cell Formulas
Range	Formula
D2	=AGGREGATE(15, 6, ROW(A1:A15)/(A1:A15=C2), 2)-AGGREGATE(15, 6, ROW(A1:A15)/(A1:A15=C2), 1)-1

 

[colin5392]

Here is one suggestion

	A	B	C	D
1	3		Find	#Rows
2	6		3	3
3	9
4	15
5	3
6	7
7	8
8	4
9	6

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula D2 =AGGREGATE(15, 6, ROW(A1:A15)/(A1:A15=C2), 2)-AGGREGATE(15, 6, ROW(A1:A15)/(A1:A15=C2), 1)-1 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks - I will give that a try (and try to figure out what it all means )

 

[MickG]

Here's another:-
This code will return numbers of rows between dups, for multi Single/duplicates in column "A". Results in column "C & D".

[COLOR="Navy"]Sub[/COLOR] MG01Jan35
[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] K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] oVal [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Cells(1, "C") = "Dup Value": Cells(1, "D") = "Rows between"
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    [COLOR="Navy"]If[/COLOR] .Item(K).Count > 1 [COLOR="Navy"]Then[/COLOR]
        oVal = 0
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] .Item(K)
            [COLOR="Navy"]If[/COLOR] oVal = 0 [COLOR="Navy"]Then[/COLOR]
                oVal = R.Row
            [COLOR="Navy"]Else[/COLOR]
               nStr = nStr & IIf(nStr = "", R.Row - oVal - 1, "," & R.Row - oVal - 1)
               oVal = R.Row
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] R
    c = c + 1
    Cells(c, "C") = K
    Cells(c, "D") = nStr
    nStr = ""
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[gfranco]

Hello.
You can also use an array formula that must be terminated with "CTRL + SHIFT + ENTER" in Office 2007 or earlier cases.

[CÓDIGO] =ROWS(INDIRECT(ADDRESS(SMALL(IF(A1:A9=C2;ROW(A1:A9)+1);1);1)&":"&ADDRESS(LARGE(IF(A1:A9=C2;ROW(A1:A9)-1);1);1)))[/ CÓDIGO]