Extract 9 matches and continue extracting next 9

[Kishan]

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

I have 9 unique patterns in the cells C4:K4 and some results in the cells D6:J44, My request is I need a macro which can extract 9 unique matches from the results and copy them in to N4:T44, </SPAN></SPAN>
Start looking in the first result row num 6 extract (and copy them in to columns N:T) as in first row find 5 look in to next row find 3 keep looking in next row find 1 when all 9 is found end find and continue next new search from the next row and follow the same search method.. </SPAN></SPAN>

Example sheet...</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
1
2
3
4			1 | 1	1 | X	1 | 2	X | 1	X | X	X | 2	2 | 1	2 | X	2 | 2
5												All 9 Out
6				1 | 1	2 | 1	1 | 1	2 | 1	X | 2	X | X	1 | 2				1 | 1	2 | 1			X | 2	X | X	1 | 2
7				2 | X	1 | 1	1 | X	2 | 1	X | X	X | X	2 | 2				2 | X		1 | X				2 | 2
8				X | 1	X | 1	1 | 2	1 | 1	X | X	1 | 2	1 | 1		9		X | 1
9				2 | 1	1 | 1	1 | 2	X | 1	1 | X	2 | X	X | 1				2 | 1	1 | 1	1 | 2	X | 1	1 | X	2 | X
10				1 | 1	1 | 2	1 | 1	X | X	1 | X	X | 2	2 | 2		9					X | X		X | 2	2 | 2
11				1 | 2	2 | 2	X | 1	1 | 1	X | 1	1 | 2	1 | 2				1 | 2	2 | 2	X | 1	1 | 1
12				1 | 1	1 | 2	2 | X	1 | X	2 | 1	X | 1	1 | 1						2 | X	1 | X	2 | 1
13				X | 1	X | 2	1 | 1	2 | 2	1 | 1	1 | X	X | 1					X | 2
14				1 | 2	1 | X	2 | 2	1 | 1	1 | 1	1 | X	1 | X
15				X | 1	1 | 1	1 | 1	1 | X	2 | 2	2 | 2	2 | X
16				1 | 1	X | 2	X | 2	1 | 1	X | 1	1 | 1	X | 1
17				X | X	2 | 1	1 | 1	X | 2	X | 1	1 | 1	X | 1		9		X | X
18				2 | 1	X | 2	1 | X	1 | X	1 | 1	1 | 1	X | X				2 | 1	X | 2	1 | X		1 | 1		X | X
19				1 | 1	1 | 1	X | X	X | 1	1 | X	X | 1	1 | X							X | 1
20				X | 1	2 | 1	X | 1	1 | 1	1 | 2	1 | 2	2 | 2								1 | 2		2 | 2
21				2 | X	2 | X	1 | 2	2 | 1	1 | X	X | 1	1 | 2		9		2 | X
22				X | 1	X | X	X | 2	1 | 1	2 | 1	2 | 2	X | 1				X | 1	X | X	X | 2	1 | 1	2 | 1	2 | 2
23				2 | 1	1 | 2	X | 2	1 | 1	1 | 1	1 | X	X | 2					1 | 2				1 | X
24				1 | 2	2 | 1	X | 2	1 | 1	1 | 1	1 | 2	X | X
25				1 | 1	X | 1	2 | 2	X | 2	2 | 2	X | X	X | 2
26				X | 2	2 | X	2 | X	1 | 2	X | X	1 | 1	1 | 1		9			2 | X
27				1 | 1	2 | 2	2 | 2	1 | X	2 | X	2 | 2	1 | 1				1 | 1	2 | 2		1 | X	2 | X	2 | 2
28				X | 1	1 | 2	X | 2	2 | 2	1 | 1	X | X	2 | 1		9		X | 1	1 | 2	X | 2			X | X
29				2 | 1	X | 1	X | X	2 | 2	1 | 1	2 | 2	2 | X				2 | 1	X | 1	X | X	2 | 2	1 | 1		2 | X
30				X | X	1 | 2	X | X	X | X	2 | X	1 | 1	X | 1					1 | 2
31				X | 1	X | X	1 | 1	1 | 1	1 | 1	2 | 1	X | 1
32				2 | X	2 | 1	X | 2	X | 2	X | X	1 | 2	1 | X		9				X | 2				1 | X
33				2 | X	1 | 1	1 | 2	2 | 2	2 | 2	2 | 1	X | X				2 | X	1 | 1	1 | 2	2 | 2		2 | 1	X | X
34				X | 1	X | X	1 | X	2 | X	2 | 1	X | 2	1 | 1		9		X | 1		1 | X			X | 2
35				1 | 2	X | X	X | X	1 | X	2 | 1	X | X	1 | 2				1 | 2	X | X		1 | X	2 | 1
36				X | X	1 | 1	1 | 2	X | 2	2 | 1	X | 1	1 | 1					1 | 1		X | 2		X | 1
37				2 | 1	1 | X	X | 1	2 | X	1 | 2	1 | X	X | X							2 | X
38				1 | 2	2 | 1	1 | 2	2 | 2	1 | 1	2 | 2	X | 1		9					2 | 2
39				2 | 1	1 | 1	1 | X	1 | 2	1 | X	X | 1	2 | 2				2 | 1	1 | 1	1 | X	1 | 2		X | 1	2 | 2
40				2 | 2	X | 2	X | 1	X | 1	1 | 1	2 | X	1 | 2					X | 2				2 | X
41				2 | 1	X | X	1 | 2	1 | 1	1 | 2	2 | 2	X | X		9								X | X
42				1 | 2	2 | X	1 | 2	2 | 2	1 | 1	X | 2	2 | X				1 | 2	2 | X	1 | 2	2 | 2	1 | 1	X | 2	2 | X
43				1 | 1	1 | X	1 | 1	2 | 2	1 | X	2 | 1	1 | X		9			1 | X				2 | 1
44				X | 1	1 | 1	1 | 1	1 | X	2 | 2	2 | 2	2 | X				X | 1	1 | 1		1 | X	2 | 2		2 | X
45
46
47
Sheet2

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

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

 

[Akuini]

Hi, Kishan. Try this:

[FONT=Lucida Console][COLOR=Royalblue]Sub[/COLOR] a1113573a()
[I][COLOR=Dimgray]'https://www.mrexcel.com/forum/excel-questions/1113573-extract-9-matches-continue-extracting-next-9-a.html[/COLOR][/I]

[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], q [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va, vb, vc
[COLOR=Royalblue]Dim[/COLOR] d [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR]

[COLOR=Royalblue]Set[/COLOR] d = CreateObject([COLOR=Darkcyan]"scripting.dictionary"[/COLOR])
vb = Range([COLOR=Darkcyan]"C4:K4"[/COLOR]).Value

va = Range([COLOR=Darkcyan]"D6:J"[/COLOR] & Cells(Rows.count, [COLOR=Darkcyan]"D"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
[COLOR=Royalblue]ReDim[/COLOR] vc([COLOR=Brown]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=Royalblue]UBound[/COLOR](va, [COLOR=Brown]1[/COLOR]), [COLOR=Brown]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=Royalblue]UBound[/COLOR](va, [COLOR=Brown]2[/COLOR]))

[COLOR=Royalblue]For[/COLOR] i = [COLOR=Brown]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=Royalblue]UBound[/COLOR](va, [COLOR=Brown]1[/COLOR])
    
    [COLOR=Royalblue]For[/COLOR] q = [COLOR=Brown]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=Royalblue]UBound[/COLOR](vb, [COLOR=Brown]2[/COLOR])
    d(vb([COLOR=Brown]1[/COLOR], q)) = [COLOR=Royalblue]Empty[/COLOR]
    [COLOR=Royalblue]Next[/COLOR]
     
     
        [COLOR=Royalblue]Do[/COLOR]
           
           [COLOR=Royalblue]For[/COLOR] j = [COLOR=Brown]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=Royalblue]UBound[/COLOR](va, [COLOR=Brown]2[/COLOR])
               
               [COLOR=Royalblue]If[/COLOR] d.Exists(va(i, j)) [COLOR=Royalblue]Then[/COLOR]
                  vc(i, j) = va(i, j)
                  d.Remove va(i, j)
                  
                  [COLOR=Royalblue]If[/COLOR] d.count = [COLOR=Brown]0[/COLOR] [COLOR=Royalblue]Then[/COLOR] Range([COLOR=Darkcyan]"L"[/COLOR] & i + [COLOR=Brown]5[/COLOR]) = [COLOR=Brown]9[/COLOR]: [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Do[/COLOR]
               [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
           
           [COLOR=Royalblue]Next[/COLOR]
        
           i = i + [COLOR=Brown]1[/COLOR]
           [COLOR=Royalblue]If[/COLOR] i > [COLOR=Royalblue]UBound[/COLOR](va, [COLOR=Brown]1[/COLOR]) [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]For[/COLOR]
        
        [COLOR=Royalblue]Loop[/COLOR]
    
[COLOR=Royalblue]Next[/COLOR]

Range([COLOR=Darkcyan]"N6"[/COLOR]).Resize([COLOR=Royalblue]UBound[/COLOR](vc, [COLOR=Brown]1[/COLOR]), [COLOR=Royalblue]UBound[/COLOR](vc, [COLOR=Brown]2[/COLOR])) = vc

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[Kishan]

Hi, Kishan. Try this:

[FONT=Lucida Console][COLOR=royalblue]Sub[/COLOR] a1113573a()
[I][COLOR=dimgray]'https://www.mrexcel.com/forum/excel-questions/1113573-extract-9-matches-continue-extracting-next-9-a.html[/COLOR][/I]

[COLOR=royalblue]Dim[/COLOR] i [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], j [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], k [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], q [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR]
[COLOR=royalblue]Dim[/COLOR] va, vb, vc
[COLOR=royalblue]Dim[/COLOR] d [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Object[/COLOR]

[COLOR=royalblue]Set[/COLOR] d = CreateObject([COLOR=darkcyan]"scripting.dictionary"[/COLOR])
vb = Range([COLOR=darkcyan]"C4:K4"[/COLOR]).Value

va = Range([COLOR=darkcyan]"D6:J"[/COLOR] & Cells(Rows.count, [COLOR=darkcyan]"D"[/COLOR]).[COLOR=royalblue]End[/COLOR](xlUp).Row)
[COLOR=royalblue]ReDim[/COLOR] vc([COLOR=brown]1[/COLOR] [COLOR=royalblue]To[/COLOR] [COLOR=royalblue]UBound[/COLOR](va, [COLOR=brown]1[/COLOR]), [COLOR=brown]1[/COLOR] [COLOR=royalblue]To[/COLOR] [COLOR=royalblue]UBound[/COLOR](va, [COLOR=brown]2[/COLOR]))

[COLOR=royalblue]For[/COLOR] i = [COLOR=brown]1[/COLOR] [COLOR=royalblue]To[/COLOR] [COLOR=royalblue]UBound[/COLOR](va, [COLOR=brown]1[/COLOR])
    
    [COLOR=royalblue]For[/COLOR] q = [COLOR=brown]1[/COLOR] [COLOR=royalblue]To[/COLOR] [COLOR=royalblue]UBound[/COLOR](vb, [COLOR=brown]2[/COLOR])
    d(vb([COLOR=brown]1[/COLOR], q)) = [COLOR=royalblue]Empty[/COLOR]
    [COLOR=royalblue]Next[/COLOR]
     
     
        [COLOR=royalblue]Do[/COLOR]
           
           [COLOR=royalblue]For[/COLOR] j = [COLOR=brown]1[/COLOR] [COLOR=royalblue]To[/COLOR] [COLOR=royalblue]UBound[/COLOR](va, [COLOR=brown]2[/COLOR])
               
               [COLOR=royalblue]If[/COLOR] d.Exists(va(i, j)) [COLOR=royalblue]Then[/COLOR]
                  vc(i, j) = va(i, j)
                  d.Remove va(i, j)
                  
                  [COLOR=royalblue]If[/COLOR] d.count = [COLOR=brown]0[/COLOR] [COLOR=royalblue]Then[/COLOR] Range([COLOR=darkcyan]"L"[/COLOR] & i + [COLOR=brown]5[/COLOR]) = [COLOR=brown]9[/COLOR]: [COLOR=royalblue]Exit[/COLOR] [COLOR=royalblue]Do[/COLOR]
               [COLOR=royalblue]End[/COLOR] [COLOR=royalblue]If[/COLOR]
           
           [COLOR=royalblue]Next[/COLOR]
        
           i = i + [COLOR=brown]1[/COLOR]
           [COLOR=royalblue]If[/COLOR] i > [COLOR=royalblue]UBound[/COLOR](va, [COLOR=brown]1[/COLOR]) [COLOR=royalblue]Then[/COLOR] [COLOR=royalblue]Exit[/COLOR] [COLOR=royalblue]For[/COLOR]
        
        [COLOR=royalblue]Loop[/COLOR]
    
[COLOR=royalblue]Next[/COLOR]

Range([COLOR=darkcyan]"N6"[/COLOR]).Resize([COLOR=royalblue]UBound[/COLOR](vc, [COLOR=brown]1[/COLOR]), [COLOR=royalblue]UBound[/COLOR](vc, [COLOR=brown]2[/COLOR])) = vc

[COLOR=royalblue]End[/COLOR] [COLOR=royalblue]Sub[/COLOR][/FONT]

Wow! Akuini, it is solved as appeal. Thank you so much for your time and support</SPAN></SPAN>
May I request does it is possible to get a missing patterns list in the columns W:AC as shown in the below example one new separate code for only this task so I can use both separately. </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
1
2
3
4			1 | 1	1 | X	1 | 2	X | 1	X | X	X | 2	2 | 1	2 | X	2 | 2
5												All 9 Out										Missings
6				1 | 1	2 | 1	1 | 1	2 | 1	X | 2	X | X	1 | 2				1 | 1	2 | 1			X | 2	X | X	1 | 2
7				2 | X	1 | 1	1 | X	2 | 1	X | X	X | X	2 | 2				2 | X		1 | X				2 | 2		4	2 | X	1 | X	2 | 2	X | 1
8				X | 1	X | 1	1 | 2	1 | 1	X | X	1 | 2	1 | 1		9		X | 1								1	X | 1
9				2 | 1	1 | 1	1 | 2	X | 1	1 | X	2 | X	X | 1				2 | 1	1 | 1	1 | 2	X | 1	1 | X	2 | X
10				1 | 1	1 | 2	1 | 1	X | X	1 | X	X | 2	2 | 2		9					X | X		X | 2	2 | 2		3	X | X		X | 2	2 | 2
11				1 | 2	2 | 2	X | 1	1 | 1	X | 1	1 | 2	1 | 2				1 | 2	2 | 2	X | 1	1 | 1
12				1 | 1	1 | 2	2 | X	1 | X	2 | 1	X | 1	1 | 1						2 | X	1 | X	2 | 1				5	2 | X	1 | X	2 | 1	X | 2	X | X
13				X | 1	X | 2	1 | 1	2 | 2	1 | 1	1 | X	X | 1					X | 2							2	X | 2	X | X
14				1 | 2	1 | X	2 | 2	1 | 1	1 | 1	1 | X	1 | X												1	X | X
15				X | 1	1 | 1	1 | 1	1 | X	2 | 2	2 | 2	2 | X												1	X | X
16				1 | 1	X | 2	X | 2	1 | 1	X | 1	1 | 1	X | 1												1	X | X
17				X | X	2 | 1	1 | 1	X | 2	X | 1	1 | 1	X | 1		9		X | X								1	X | X
18				2 | 1	X | 2	1 | X	1 | X	1 | 1	1 | 1	X | X				2 | 1	X | 2	1 | X		1 | 1		X | X
19				1 | 1	1 | 1	X | X	X | 1	1 | X	X | 1	1 | X							X | 1					4	X | 1	1 | 2	2 | 2	2 | X
20				X | 1	2 | 1	X | 1	1 | 1	1 | 2	1 | 2	2 | 2								1 | 2		2 | 2		3	1 | 2	2 | 2	2 | X
21				2 | X	2 | X	1 | 2	2 | 1	1 | X	X | 1	1 | 2		9		2 | X								1	2 | X
22				X | 1	X | X	X | 2	1 | 1	2 | 1	2 | 2	X | 1				X | 1	X | X	X | 2	1 | 1	2 | 1	2 | 2
23				2 | 1	1 | 2	X | 2	1 | 1	1 | 1	1 | X	X | 2					1 | 2				1 | X			3	1 | 2	1 | X	2 | X
24				1 | 2	2 | 1	X | 2	1 | 1	1 | 1	1 | 2	X | X												1	2 | X
25				1 | 1	X | 1	2 | 2	X | 2	2 | 2	X | X	X | 2												1	2 | X
26				X | 2	2 | X	2 | X	1 | 2	X | X	1 | 1	1 | 1		9			2 | X							1	3 | X
27				1 | 1	2 | 2	2 | 2	1 | X	2 | X	2 | 2	1 | 1				1 | 1	2 | 2		1 | X	2 | X
28				X | 1	1 | 2	X | 2	2 | 2	1 | 1	X | X	2 | 1		9		X | 1	1 | 2	X | 2			X | X	2 | 1		5	X | 1	1 | 2	X | 2	X | X	2 | 1
29				2 | 1	X | 1	X | X	2 | 2	1 | 1	2 | 2	2 | X				2 | 1	X | 1	X | X	2 | 2	1 | 1		2 | X
30				X | X	1 | 2	X | X	X | X	2 | X	1 | 1	X | 1					1 | 2							3	1 | 2	X | 2	1 | X
31				X | 1	X | X	1 | 1	1 | 1	1 | 1	2 | 1	X | 1												2	X | 2	1 | X
32				2 | X	2 | 1	X | 2	X | 2	X | X	1 | 2	1 | X		9				X | 2				1 | X		1	X | 2	1 | X
33				2 | X	1 | 1	1 | 2	2 | 2	2 | 2	2 | 1	X | X				2 | X	1 | 1	1 | 2	2 | 2		2 | 1	X | X
34				X | 1	X | X	1 | X	2 | X	2 | 1	X | 2	1 | 1		9		X | 1		1 | X			X | 2			3	X | 1	1 | X	X | 2
35				1 | 2	X | X	X | X	1 | X	2 | 1	X | X	1 | 2				1 | 2	X | X		1 | X	2 | 1
36				X | X	1 | 1	1 | 2	X | 2	2 | 1	X | 1	1 | 1					1 | 1		X | 2		X | 1			5	1 | 1	X | 2	X | 1	2 | X	2 | 2
37				2 | 1	1 | X	X | 1	2 | X	1 | 2	1 | X	X | X							2 | X					1	2 | 2
38				1 | 2	2 | 1	1 | 2	2 | 2	1 | 1	2 | 2	X | 1		9					2 | 2					1	3 | 2
39				2 | 1	1 | 1	1 | X	1 | 2	1 | X	X | 1	2 | 2				2 | 1	1 | 1	1 | X	1 | 2		X | 1	2 | 2
40				2 | 2	X | 2	X | 1	X | 1	1 | 1	2 | X	1 | 2					X | 2				2 | X			3	X | 2	2 | X	X | X
41				2 | 1	X | X	1 | 2	1 | 1	1 | 2	2 | 2	X | X		9			X | X							1	X | X
42				1 | 2	2 | X	1 | 2	2 | 2	1 | 1	X | 2	2 | X				1 | 2	2 | X		2 | 2	1 | 1	X | 2
43				1 | 1	1 | X	1 | 1	2 | 2	1 | X	2 | 1	1 | X					1 | X				2 | 1			4	1 | X	2 | 1	X | 1	X | X
44				X | 1	1 | 1	1 | 1	1 | X	2 | 2	2 | 2	2 | X				X | 1								2	X | 1	X | X
45																						1	X | X
46
47
Sheet3

Good Luck</SPAN></SPAN>

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

 

[Akuini]

Sorry, I tried amending the code according to the new requirements but it didn't work.
Maybe someone else here can help you or you can start a new thread because this is a new problem anyway.

 

[Kishan]

Sorry, I tried amending the code according to the new requirements but it didn't work.

Hi Akuini, the main were find 9 patterns in the results column and extract them and continue search for next, which you solved absolutely amazing. I do appreciate your help very much. </SPAN></SPAN>

Maybe someone else here can help you or you can start a new thread because this is a new problem anyway.

The second part I thought after I got the result by your code and tell you the truth even it is not clear to me the way I have place the question in the post#3 it look confusing to me also it is vary fare I will start a new thread and will try to put it in a better way. </SPAN></SPAN>

Have a good day</SPAN></SPAN>

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