Extract 9 matches and continue extracting next 9

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,645
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUV
1
2
3
41 | 11 | X1 | 2X | 1X | XX | 22 | 12 | X2 | 2
5All 9 Out
61 | 12 | 11 | 12 | 1X | 2X | X1 | 21 | 12 | 1X | 2X | X1 | 2
72 | X1 | 11 | X2 | 1X | XX | X2 | 22 | X1 | X2 | 2
8X | 1X | 11 | 21 | 1X | X1 | 21 | 19X | 1
92 | 11 | 11 | 2X | 11 | X2 | XX | 12 | 11 | 11 | 2X | 11 | X2 | X
101 | 11 | 21 | 1X | X1 | XX | 22 | 29X | XX | 22 | 2
111 | 22 | 2X | 11 | 1X | 11 | 21 | 21 | 22 | 2X | 11 | 1
121 | 11 | 22 | X1 | X2 | 1X | 11 | 12 | X1 | X2 | 1
13X | 1X | 21 | 12 | 21 | 11 | XX | 1X | 2
141 | 21 | X2 | 21 | 11 | 11 | X1 | X
15X | 11 | 11 | 11 | X2 | 22 | 22 | X
161 | 1X | 2X | 21 | 1X | 11 | 1X | 1
17X | X2 | 11 | 1X | 2X | 11 | 1X | 19X | X
182 | 1X | 21 | X1 | X1 | 11 | 1X | X2 | 1X | 21 | X1 | 1X | X
191 | 11 | 1X | XX | 11 | XX | 11 | XX | 1
20X | 12 | 1X | 11 | 11 | 21 | 22 | 21 | 22 | 2
212 | X2 | X1 | 22 | 11 | XX | 11 | 292 | X
22X | 1X | XX | 21 | 12 | 12 | 2X | 1X | 1X | XX | 21 | 12 | 12 | 2
232 | 11 | 2X | 21 | 11 | 11 | XX | 21 | 21 | X
241 | 22 | 1X | 21 | 11 | 11 | 2X | X
251 | 1X | 12 | 2X | 22 | 2X | XX | 2
26X | 22 | X2 | X1 | 2X | X1 | 11 | 192 | X
271 | 12 | 22 | 21 | X2 | X2 | 21 | 11 | 12 | 21 | X2 | X2 | 2
28X | 11 | 2X | 22 | 21 | 1X | X2 | 19X | 11 | 2X | 2X | X
292 | 1X | 1X | X2 | 21 | 12 | 22 | X2 | 1X | 1X | X2 | 21 | 12 | X
30X | X1 | 2X | XX | X2 | X1 | 1X | 11 | 2
31X | 1X | X1 | 11 | 11 | 12 | 1X | 1
322 | X2 | 1X | 2X | 2X | X1 | 21 | X9X | 21 | X
332 | X1 | 11 | 22 | 22 | 22 | 1X | X2 | X1 | 11 | 22 | 22 | 1X | X
34X | 1X | X1 | X2 | X2 | 1X | 21 | 19X | 11 | XX | 2
351 | 2X | XX | X1 | X2 | 1X | X1 | 21 | 2X | X1 | X2 | 1
36X | X1 | 11 | 2X | 22 | 1X | 11 | 11 | 1X | 2X | 1
372 | 11 | XX | 12 | X1 | 21 | XX | X2 | X
381 | 22 | 11 | 22 | 21 | 12 | 2X | 192 | 2
392 | 11 | 11 | X1 | 21 | XX | 12 | 22 | 11 | 11 | X1 | 2X | 12 | 2
402 | 2X | 2X | 1X | 11 | 12 | X1 | 2X | 22 | X
412 | 1X | X1 | 21 | 11 | 22 | 2X | X9X | X
421 | 22 | X1 | 22 | 21 | 1X | 22 | X1 | 22 | X1 | 22 | 21 | 1X | 22 | X
431 | 11 | X1 | 12 | 21 | X2 | 11 | X91 | X2 | 1
44X | 11 | 11 | 11 | X2 | 22 | 22 | XX | 11 | 11 | X2 | 22 | X
45
46
47
Sheet2


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

Regards,
</SPAN></SPAN>
Kishan
</SPAN></SPAN>
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi, Kishan. Try this:

Code:
[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]
 
Upvote 0
Hi, Kishan. Try this:

Code:
[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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1
2
3
41 | 11 | X1 | 2X | 1X | XX | 22 | 12 | X2 | 2
5All 9 OutMissings
61 | 12 | 11 | 12 | 1X | 2X | X1 | 21 | 12 | 1X | 2X | X1 | 2
72 | X1 | 11 | X2 | 1X | XX | X2 | 22 | X1 | X2 | 242 | X1 | X2 | 2X | 1
8X | 1X | 11 | 21 | 1X | X1 | 21 | 19X | 11X | 1
92 | 11 | 11 | 2X | 11 | X2 | XX | 12 | 11 | 11 | 2X | 11 | X2 | X
101 | 11 | 21 | 1X | X1 | XX | 22 | 29X | XX | 22 | 23X | XX | 22 | 2
111 | 22 | 2X | 11 | 1X | 11 | 21 | 21 | 22 | 2X | 11 | 1
121 | 11 | 22 | X1 | X2 | 1X | 11 | 12 | X1 | X2 | 152 | X1 | X2 | 1X | 2X | X
13X | 1X | 21 | 12 | 21 | 11 | XX | 1X | 22X | 2X | X
141 | 21 | X2 | 21 | 11 | 11 | X1 | X1X | X
15X | 11 | 11 | 11 | X2 | 22 | 22 | X1X | X
161 | 1X | 2X | 21 | 1X | 11 | 1X | 11X | X
17X | X2 | 11 | 1X | 2X | 11 | 1X | 19X | X1X | X
182 | 1X | 21 | X1 | X1 | 11 | 1X | X2 | 1X | 21 | X1 | 1X | X
191 | 11 | 1X | XX | 11 | XX | 11 | XX | 14X | 11 | 22 | 22 | X
20X | 12 | 1X | 11 | 11 | 21 | 22 | 21 | 22 | 231 | 22 | 22 | X
212 | X2 | X1 | 22 | 11 | XX | 11 | 292 | X12 | X
22X | 1X | XX | 21 | 12 | 12 | 2X | 1X | 1X | XX | 21 | 12 | 12 | 2
232 | 11 | 2X | 21 | 11 | 11 | XX | 21 | 21 | X31 | 21 | X2 | X
241 | 22 | 1X | 21 | 11 | 11 | 2X | X12 | X
251 | 1X | 12 | 2X | 22 | 2X | XX | 212 | X
26X | 22 | X2 | X1 | 2X | X1 | 11 | 192 | X13 | X
271 | 12 | 22 | 21 | X2 | X2 | 21 | 11 | 12 | 21 | X2 | X
28X | 11 | 2X | 22 | 21 | 1X | X2 | 19X | 11 | 2X | 2X | X2 | 15X | 11 | 2X | 2X | X2 | 1
292 | 1X | 1X | X2 | 21 | 12 | 22 | X2 | 1X | 1X | X2 | 21 | 12 | X
30X | X1 | 2X | XX | X2 | X1 | 1X | 11 | 231 | 2X | 21 | X
31X | 1X | X1 | 11 | 11 | 12 | 1X | 12X | 21 | X
322 | X2 | 1X | 2X | 2X | X1 | 21 | X9X | 21 | X1X | 21 | X
332 | X1 | 11 | 22 | 22 | 22 | 1X | X2 | X1 | 11 | 22 | 22 | 1X | X
34X | 1X | X1 | X2 | X2 | 1X | 21 | 19X | 11 | XX | 23X | 11 | XX | 2
351 | 2X | XX | X1 | X2 | 1X | X1 | 21 | 2X | X1 | X2 | 1
36X | X1 | 11 | 2X | 22 | 1X | 11 | 11 | 1X | 2X | 151 | 1X | 2X | 12 | X2 | 2
372 | 11 | XX | 12 | X1 | 21 | XX | X2 | X12 | 2
381 | 22 | 11 | 22 | 21 | 12 | 2X | 192 | 213 | 2
392 | 11 | 11 | X1 | 21 | XX | 12 | 22 | 11 | 11 | X1 | 2X | 12 | 2
402 | 2X | 2X | 1X | 11 | 12 | X1 | 2X | 22 | X3X | 22 | XX | X
412 | 1X | X1 | 21 | 11 | 22 | 2X | X9X | X1X | X
421 | 22 | X1 | 22 | 21 | 1X | 22 | X1 | 22 | X2 | 21 | 1X | 2
431 | 11 | X1 | 12 | 21 | X2 | 11 | X1 | X2 | 141 | X2 | 1X | 1X | X
44X | 11 | 11 | 11 | X2 | 22 | 22 | XX | 12X | 1X | X
451X | X
46
47
Sheet3


Good Luck
</SPAN></SPAN>

Kind Regards
</SPAN></SPAN>
Kishan :biggrin:
</SPAN></SPAN>
 
Last edited:
Upvote 0
Sorry, I tried amending the code according to the new requirements but it didn't work.:confused:
Maybe someone else here can help you or you can start a new thread because this is a new problem anyway.
 
Upvote 0
Sorry, I tried amending the code according to the new requirements but it didn't work.:confused:
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>

 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top