Extract unmatched of 9 patterns till the 9 patterns remains 0 and continue new search

[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 unmatched patterns after the matching results out of 9 patterns and copy them in to N4:T44. </SPAN></SPAN>

Start looking in the first result row num 6 as in first row find 5 match, remaining 4 unmatched patterns copy them in to columns N:T, look in to next result row num 7 found 3 more matches, now remaining 1 unmatched pattern copy them in to columns N:T, keep looking in to next result row num 8 found 1 more match, now remaining unmatched 0 exit, And continue next new search in to the next rows 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														Remaining	Remaining	Remaining	Remaining	Remaining	Remaining	Remaining
3			Patt1	Patt2	Patt3	Patt4	Patt5	Patt6	Patt7	Patt8	Patt9			Unmatched	Unmatched	Unmatched	Unmatched	Unmatched	Unmatched	Unmatched
4			1 | 1	1 | X	1 | 2	X | 1	X | X	X | 2	2 | 1	2 | X	2 | 2			Of 9 Patt	Of 9 Patt	Of 9 Patt	Of 9 Patt	Of 9 Patt	Of 9 Patt	Of 9 Patt
5												All 9 Out	Remaining
6				1 | 1	2 | 1			X | 2	X | X	1 | 2				2 | X	1 | X	2 | 2	X | 1
7				2 | X		1 | X				2 | 2				X | 1
8				X | 1								9	0
9				2 | 1	1 | 1	1 | 2	X | 1	1 | X	2 | X					X | X	X | 2	2 | 2
10							X | X		X | 2	2 | 2		9	0
11				1 | 2	2 | 2	X | 1	1 | 1							2 | X	1 | X	2 | 1	X | 2	X | X
12						2 | X	1 | X	2 | 1						X | 2	X | X
13					X | 2									X | X
14														X | X
15														X | X
16														X | X
17				X | X								9	0
18				2 | 1	X | 2	1 | X		1 | 1		X | X				X | 1	1 | 2	2 | 2	2 | X
19							X | 1							1 | 2	2 | 2	2 | X
20								1 | 2		2 | 2				2 | X
21				2 | X								9	0
22				X | 1	X | X	X | 2	1 | 1	2 | 1	2 | 2					1 | 2	1 | X	2 | X
23					1 | 2				1 | X					2 | X
24														2 | X
25														2 | X
26					2 | X							9	0
27				1 | 1	2 | 2		1 | X	2 | X						X | 1	1 | 2	X | 2	X | X	2 | 1
28				X | 1	1 | 2	X | 2			X | X	2 | 1		9	0
29				2 | 1	X | 1	X | X	2 | 2	1 | 1		2 | X				1 | 2	X | 2	1 | X
30					1 | 2									X | 2	1 | X
31														X | 2	1 | X
32						X | 2				1 | X		9	0
33				2 | X	1 | 1	1 | 2	2 | 2		2 | 1	X | X				X | 1	1 | X	X | 2
34				X | 1		1 | X			X | 2			9	0
35				1 | 2	X | X		1 | X	2 | 1						1 | 1	X | 2	X | 1	2 | X	2 | 2
36					1 | 1		X | 2		X | 1					2 | 2
37							2 | X							2 | 2
38							2 | 2					9	0
39				2 | 1	1 | 1	1 | X	1 | 2		X | 1	2 | 2				X | 2	2 | X	X | X
40					X | 2				2 | X					X | X
41					X | X							9	0
42				1 | 2	2 | X		2 | 2	1 | 1	X | 2					1 | X	2 | 1	X | 1	X | X
43					1 | X				2 | 1					X | 1	X | X
44				X | 1										X | X
45
46
47
Sheet4

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

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

 

[Trebor76]

Hi Kishan,

Try this:

Option Explicit
Sub Macro1()

    Dim clnMyUniqueArray As New Collection
    Dim rngMyCell As Range
    Dim lngLastRow As Long
    Dim lngMyRow As Long
    Dim lngMyCol As Long
    Dim i As Integer
    
    Application.ScreenUpdating = False

    For Each rngMyCell In Range("C4:K4")
        clnMyUniqueArray.Add rngMyCell, CStr(rngMyCell)
    Next rngMyCell
    
    lngLastRow = Range("C:K").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For lngMyRow = 6 To lngLastRow
        For lngMyCol = 3 To 11 'Columns C to K
            If Len(Cells(lngMyRow, lngMyCol)) > 0 Then
                For i = 1 To clnMyUniqueArray.Count
                    If CStr(Cells(lngMyRow, lngMyCol)) = CStr(clnMyUniqueArray(i)) Then
                        clnMyUniqueArray.Remove (i)
                        Exit For
                    End If
                Next i
            End If
        Next lngMyCol
        If clnMyUniqueArray.Count > 0 Then
            For i = 1 To clnMyUniqueArray.Count
                Range("N" & lngMyRow).Offset(0, i - 1).Value = CStr(clnMyUniqueArray(i))
            Next i
        Else
            For Each rngMyCell In Range("C4:K4")
                clnMyUniqueArray.Add rngMyCell, CStr(rngMyCell)
            Next rngMyCell
            Range("L" & lngMyRow).Value = clnMyUniqueArray.Count
            Range("M" & lngMyRow).Value = 0
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True
    
End Sub

Note the remaining unmatched items for each row are outputted in the same order as they were added from the array i.e. cells C4:K4) not how you've shown (hope that's not a problem).

Regards,

Robert

 

[Kishan]

Hi Kishan,

Try this:

Option Explicit
Sub Macro1()

    Dim clnMyUniqueArray As New Collection
    Dim rngMyCell As Range
    Dim lngLastRow As Long
    Dim lngMyRow As Long
    Dim lngMyCol As Long
    Dim i As Integer
    
    Application.ScreenUpdating = False

    For Each rngMyCell In Range("C4:K4")
        clnMyUniqueArray.Add rngMyCell, CStr(rngMyCell)
    Next rngMyCell
    
    lngLastRow = Range("C:K").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For lngMyRow = 6 To lngLastRow
        For lngMyCol = 3 To 11 'Columns C to K
            If Len(Cells(lngMyRow, lngMyCol)) > 0 Then
                For i = 1 To clnMyUniqueArray.Count
                    If CStr(Cells(lngMyRow, lngMyCol)) = CStr(clnMyUniqueArray(i)) Then
                        clnMyUniqueArray.Remove (i)
                        Exit For
                    End If
                Next i
            End If
        Next lngMyCol
        If clnMyUniqueArray.Count > 0 Then
            For i = 1 To clnMyUniqueArray.Count
                Range("N" & lngMyRow).Offset(0, i - 1).Value = CStr(clnMyUniqueArray(i))
            Next i
        Else
            For Each rngMyCell In Range("C4:K4")
                clnMyUniqueArray.Add rngMyCell, CStr(rngMyCell)
            Next rngMyCell
            Range("L" & lngMyRow).Value = clnMyUniqueArray.Count
            Range("M" & lngMyRow).Value = 0
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True
    
End Sub

Note the remaining unmatched items for each row are outputted in the same order as they were added from the array i.e. cells C4:K4) not how you've shown
(hope that's not a problem).

Regards,

Robert

Wonderful! Robert, macro is extremely perfect. Even I like the unmatched pattern order as they are showing now as per cells (C4:K4) orders.</SPAN></SPAN>

I do appreciate your help and time you spent to solve my query </SPAN></SPAN>
Have a good day and Good Luck</SPAN></SPAN>

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

 

[Trebor76]

Thanks for letting us know and you're welcome Thanks also for the thanks and likes