Find "Start and End" row number of the each unique pattern

[Kishan]

Using Excel 2000</SPAN></SPAN>
Hi,</SPAN></SPAN>

Column C got repeated patterns (or can be said unique patterns)</SPAN></SPAN>
I want to list the row number of each unique set "Start row number" & the "End row number" in the column E:F</SPAN></SPAN>

Example data</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G
1
2
3
4			Patt		Start	End
5			Patt		Row Num	Row Num
6			0 | 0 | 0 | 0 | 0 | 0 | 2		6	8
7			0 | 0 | 0 | 0 | 0 | 0 | 2		9	12
8			0 | 0 | 0 | 0 | 0 | 0 | 2		13	15
9			0 | 0 | 0 | 0 | 0 | 1 | 1		16	19
10			0 | 0 | 0 | 0 | 0 | 1 | 1		20	23
11			0 | 0 | 0 | 0 | 0 | 1 | 1		24	26
12			0 | 0 | 0 | 0 | 0 | 1 | 1		27	30
13			0 | 0 | 0 | 0 | 0 | 2 | 0		31	34
14			0 | 0 | 0 | 0 | 0 | 2 | 0		35	38
15			0 | 0 | 0 | 0 | 0 | 2 | 0		39	41
16			0 | 0 | 0 | 0 | 1 | 0 | 1		42	45
17			0 | 0 | 0 | 0 | 1 | 0 | 1		46	57
18			0 | 0 | 0 | 0 | 1 | 0 | 1		58	68
19			0 | 0 | 0 | 0 | 1 | 0 | 1
20			0 | 0 | 0 | 0 | 1 | 1 | 0
21			0 | 0 | 0 | 0 | 1 | 1 | 0
22			0 | 0 | 0 | 0 | 1 | 1 | 0
23			0 | 0 | 0 | 0 | 1 | 1 | 0
24			0 | 0 | 0 | 0 | 2 | 0 | 0
25			0 | 0 | 0 | 0 | 2 | 0 | 0
26			0 | 0 | 0 | 0 | 2 | 0 | 0
27			0 | 0 | 0 | 1 | 0 | 0 | 1
28			0 | 0 | 0 | 1 | 0 | 0 | 1
29			0 | 0 | 0 | 1 | 0 | 0 | 1
30			0 | 0 | 0 | 1 | 0 | 0 | 1
31			0 | 0 | 0 | 1 | 0 | 1 | 0
32			0 | 0 | 0 | 1 | 0 | 1 | 0
33			0 | 0 | 0 | 1 | 0 | 1 | 0
34			0 | 0 | 0 | 1 | 0 | 1 | 0
35			0 | 0 | 0 | 1 | 1 | 0 | 0
36			0 | 0 | 0 | 1 | 1 | 0 | 0
37			0 | 0 | 0 | 1 | 1 | 0 | 0
38			0 | 0 | 0 | 1 | 1 | 0 | 0
39			0 | 0 | 0 | 2 | 0 | 0 | 0
40			0 | 0 | 0 | 2 | 0 | 0 | 0
41			0 | 0 | 0 | 2 | 0 | 0 | 0
42			0 | 0 | 1 | 0 | 0 | 0 | 1
43			0 | 0 | 1 | 0 | 0 | 0 | 1
44			0 | 0 | 1 | 0 | 0 | 0 | 1
45			0 | 0 | 1 | 0 | 0 | 0 | 1
46			0 | 0 | 1 | 0 | 0 | 0 | 1
47			0 | 0 | 1 | 0 | 0 | 0 | 1
48			0 | 0 | 1 | 0 | 0 | 0 | 1
49			0 | 0 | 1 | 0 | 0 | 0 | 1
50			0 | 0 | 1 | 0 | 0 | 0 | 1
51			0 | 0 | 1 | 0 | 0 | 0 | 1
52			0 | 0 | 1 | 0 | 0 | 0 | 1
53			0 | 0 | 1 | 0 | 0 | 0 | 1
54			0 | 0 | 1 | 0 | 0 | 0 | 1
55			0 | 0 | 1 | 0 | 0 | 0 | 1
56			0 | 0 | 1 | 0 | 0 | 0 | 1
57			0 | 0 | 1 | 0 | 0 | 0 | 1
58			0 | 0 | 2 | 0 | 0 | 0 | 0
59			0 | 0 | 2 | 0 | 0 | 0 | 0
60			0 | 0 | 2 | 0 | 0 | 0 | 0
61			0 | 0 | 2 | 0 | 0 | 0 | 0
62			0 | 0 | 2 | 0 | 0 | 0 | 0
63			0 | 0 | 2 | 0 | 0 | 0 | 0
64			0 | 0 | 2 | 0 | 0 | 0 | 0
65			0 | 0 | 2 | 0 | 0 | 0 | 0
66			0 | 0 | 2 | 0 | 0 | 0 | 0
67			0 | 0 | 2 | 0 | 0 | 0 | 0
68			0 | 0 | 2 | 0 | 0 | 0 | 0
69
70
71
Sheet1

Thank you in advance</SPAN></SPAN>

Regards,</SPAN>
Kishan</SPAN></SPAN>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG18Oct38
[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] Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range("C6", Range("C" & 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, Array(Dn.Row, Dn.Row)
    [COLOR="Navy"]Else[/COLOR]
        Q = .Item(Dn.Value)
            Q(1) = Dn.Row
        .Item(Dn.Value) = Q
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Range("E6").Resize(.Count, 2) = Application.Transpose(Application.Transpose(.items))
[COLOR="Navy"]End[/COLOR] With

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

Regards Mick

 

[steve the fish]

Formulas:

In E6:

=ROW(C6)

In F6:

=LOOKUP(2,1/($C$6:$C$68=INDEX($C$6:$C$68,E6-ROW($C$6)+1)),ROW($C$6:$C$68))

In E7:

=F6+1

Drag down on E7 and F6 until you start getting errors. You have then run out of data. It requires sorted data as you presented.

 

[Kishan]

Formulas:

In E6:

=ROW(C6)

In F6:

=LOOKUP(2,1/($C$6:$C$68=INDEX($C$6:$C$68,E6-ROW($C$6)+1)),ROW($C$6:$C$68))

In E7:

=F6+1

Drag down on E7 and F6 until you start getting errors. You have then run out of data. It requires sorted data as you presented.

steve the fish, tried the formula it worked

Thank you for the help

Kind Regards,
Kishan

 

[Kishan]

Try this:-

[COLOR=navy]Sub[/COLOR] MG18Oct38
[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] Q [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Set[/COLOR] Rng = Range("C6", Range("C" & 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, Array(Dn.Row, Dn.Row)
    [COLOR=navy]Else[/COLOR]
        Q = .Item(Dn.Value)
            Q(1) = Dn.Row
        .Item(Dn.Value) = Q
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
Range("E6").Resize(.Count, 2) = Application.Transpose(Application.Transpose(.items))
[COLOR=navy]End[/COLOR] With

[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

MickG, I liked the macro solution it worked fine!! It is an easy and also quick and accurate while working with enormous data </SPAN></SPAN>

Thank you for your time and help</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>

 

[MickG]

You're very welcome