Find the all rows number of the each pattern

[Kishan]

Using Excel 2000</SPAN></SPAN>

Hi,</SPAN></SPAN>

I want to find the all rows number of the each pattern</SPAN></SPAN>

Below example shows the row numbers only for first 2 patterns, it is difficult to find manually.</SPAN></SPAN>

Please need VBA help?</SPAN></SPAN>

Result</SPAN></SPAN> data example</SPAN></SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1			Cycle1
2			CycleX
3			Cycle2
4			Next Delay-->
5			EM		1|1|1	1|1|X	1|1|2	1|X|1	1|X|X	1|X|2	1|2|1	1|2|X	1|2|2		X|1|1	X|1|X	X|1|2	X|X|1	X|X|X	X|X|2	X|2|1	X|2|X	X|2|2		2|1|1	2|1|X	2|1|2	2|X|1	2|X|X	2|X|2	2|2|1	2|2|X	2|2|2
6			X|1|2		16	26
7			1|X|2		20	32
8			1|X|1		22	57
9			1|X|1		23	59
10			X|1|X		24	68
11			X|X|X		48	72
12			2|X|1		50
13			X|1|X		51
14			1|X|X		52
15			X|2|X		55
16			1|1|1		61
17			1|2|1		63
18			1|X|X		64
19			1|X|X		71
20			1|1|1
21			X|1|2
22			1|1|1
23			1|1|1
24			1|1|1
25			1|2|2
26			1|1|X
27			X|X|X
28			X|2|1
29			1|X|2
30			2|1|X
31			1|X|1
32			1|1|X
33			2|1|X
34			X|1|1
35			1|2|1
36			X|1|1
37			2|X|1
38			1|X|2
39			X|1|X
40			1|X|X
41			1|X|1
42			1|2|1
43			X|1|1
44			2|X|X
45			X|2|1
46			1|X|X
47			X|X|1
48			1|1|1
49			X|X|1
50			1|1|1
51			1|1|1
52			1|1|1
53			2|X|1
54			1|X|X
55			1|1|1
56			X|1|1
57			1|1|X
58			1|2|2
59			1|1|X
60			1|2|2
61			1|1|1
62			2|1|1
63			1|1|1
64			1|1|1
65			1|2|1
66			1|2|2
67			1|1|1
68			1|1|X
69			1|X|1
70			X|1|1
71			1|1|1
72			1|1|X
73			1|X|X
74			X|X|1
75			1|1|2
76
77
Sheet1

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

Regards,</SPAN>
Kishan</SPAN>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG01Sep27
[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] Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(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, Dn.Row
    [COLOR="Navy"]Else[/COLOR]
        .Item(Dn.Value) = .Item(Dn.Value) & ", " & Dn.Row
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]

[COLOR="Navy"]Set[/COLOR] Rng = Range("E5", Cells(5, Columns.Count).End(xlToLeft))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            Sp = Split(.Item(Dn.Value), ", ")
            Dn.Offset(1).Resize(UBound(Sp) + 1).Value = Application.Transpose(Sp)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Kishan]

Try this:-

[COLOR=navy]Sub[/COLOR] MG01Sep27
[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] Sp [COLOR=navy]As[/COLOR] Variant
[COLOR=navy]Set[/COLOR] Rng = Range(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, Dn.Row
    [COLOR=navy]Else[/COLOR]
        .Item(Dn.Value) = .Item(Dn.Value) & ", " & Dn.Row
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]

[COLOR=navy]Set[/COLOR] Rng = Range("E5", Cells(5, Columns.Count).End(xlToLeft))
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        [COLOR=navy]If[/COLOR] .exists(Dn.Value) [COLOR=navy]Then[/COLOR]
            Sp = Split(.Item(Dn.Value), ", ")
            Dn.Offset(1).Resize(UBound(Sp) + 1).Value = Application.Transpose(Sp)
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Hi Mick, it is just amazing, perfect results!!</SPAN></SPAN>

Thank you for quick response, solved totally</SPAN></SPAN>

Have a great weekend</SPAN></SPAN>

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