Excel VBA comma separated data comparison

[bobbyexcel]

Can someone help me on my below requirement please. My data looks like this and I want to check the rows matching or not but they are comma separated..
I've A,B columns and want to get the result(Match or not) in column C.

Book2
	A	B	C
1	Data 1	Data 2	Result
2	abc, bcc	bcc, abc	Match
3	xyz, mno	abc, xyz	UnMatch
4	mno, tuv	mno, tuv	Match
5	abb, bbc	abb, bbc	Match
Sheet1

 

[DanteAmor]

Maybe it will help you with formula:

varios 23jun2022 rango con indice.xlsm
	A	B	C
1	Data 1	Data 2	Result
2	abc, bcc	bcc, abc	Match
3	xyz, mno	abc, xyz	UnMatch
4	mno, tuv	mno, tuv	Match
5	abb, bbc	abb, bbc	Match
Hoja1

Cell Formulas
Range	Formula
C2:C5	C2	=IF(SUM(--ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(B2, ",", REPT(" ",255)),255*(ROW($1:$2)-1)+1,255)),A2)))=2,"Match", "UnMatch")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bobbyexcel]

It is not working if you have cell value like ab,cbcc.. It says matched but not..

 

[DanteAmor]

Try:

Dante Amor
	A	B	C
1	Data 1	Data 2	Result
2	abc, bcc	bcc, abc	Match
3	xyz, mno	abc, xyz	UnMatch
4	mno, tuv	mno, tuv	Match
5	abb, bbc	abb, bbc	Match
6	ab, cbcc	ab, bbcc	UnMatch
7	ab, cbcc	ab, cbcc	Match
Hoja1

Cell Formulas
Range	Formula
C2:C7	C2	=IF(SUM(--ISNUMBER(SEARCH(","&TRIM(MID(SUBSTITUTE(B2, ",", REPT(" ",255)),255*(ROW($1:$2)-1)+1,255))&",",","&TRIM(SUBSTITUTE(A2,", ",","))&",")))=2,"Match", "UnMatch")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rick Rothstein]

It is not working if you have cell value like ab,cbcc.. It says matched but not..

All you examples showed two 3 character substrings separated by a comma so obviously your example was not truly representative. Any other surprises for us, like more than two substrings per cell?

 

[bobbyexcel]

Try:

Dante Amor
	A	B	C
1	Data 1	Data 2	Result
2	abc, bcc	bcc, abc	Match
3	xyz, mno	abc, xyz	UnMatch
4	mno, tuv	mno, tuv	Match
5	abb, bbc	abb, bbc	Match
6	ab, cbcc	ab, bbcc	UnMatch
7	ab, cbcc	ab, cbcc	Match
Hoja1

Cell Formulas
Range	Formula
C2:C7	C2	=IF(SUM(--ISNUMBER(SEARCH(","&TRIM(MID(SUBSTITUTE(B2, ",", REPT(" ",255)),255*(ROW($1:$2)-1)+1,255))&",",","&TRIM(SUBSTITUTE(A2,", ",","))&",")))=2,"Match", "UnMatch")
Press CTRL+SHIFT+ENTER to enter array formulas.

This working as expected but how do I incorporate into VBA code. It says expected end of statement.

Dim lastrownew2 As Integer
    lastrownew2 = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
   
    For Each c In Range("D2:D" & lastrownew2
        c.value = "{=IF(SUM(--ISNUMBER(SEARCH(","&TRIM(MID(SUBSTITUTE(F &c.row, ",", REPT(" ",255)),255*(ROW($1:$2)-1)+1,255))&",",","&TRIM(SUBSTITUTE(G&c.row,", ",","))&",")))=2,"Match", "UnMatch")}"
        c.Formula = c.Value
    Next

 

[bobbyexcel]

All you examples showed two 3 character substrings separated by a comma so obviously your example was not truly representative. Any other surprises for us, like more than two substrings per cell?

All my actual data has only 1 character but I just gave one example with 3 chars separated with comma.

Eg.. this is how my data looks

Book3
	B	C
3	s,2	2,s
4	3,s	3,s
5	4,s	s,s
6	s,s	4,s
Sheet1

 

[Peter_SSs]

but I just gave one example with 3 chars separated with comma.

Actually, your original samples were separated by comma and space.
Assuming comma only, here are two more to test, depending on whether or not you have the TEXTSPLIT function, column C, otherwise column D

22 06 24.xlsm
	A	B	C	D
1	Data 1	Data 2	Result 1	Result 2
2	abc,bcc	bcc,abc	Match	Match
3	xyz,mno	abc,xyz	Unmatch	Unmatch
4	mno,tuv	mno,tuv	Match	Match
5	abb,bbc	abb,bbc	Match	Match
6	s,2	2,s	Match	Match
7	3,s	3,s	Match	Match
8	4,s	s,s	Unmatch	Unmatch
9	s,s	4,s	Unmatch	Unmatch
Compare columns

Cell Formulas
Range	Formula
C2:C9	C2	=IF(PRODUCT(--(SORT(TEXTSPLIT(A2,","),,,1)=SORT(TEXTSPLIT(B2,","),,,1))),"Match","Unmatch")
D2:D9	D2	=IF(PRODUCT(--(SORT(FILTERXML("<p><c>"&SUBSTITUTE(A2,",","</c><c>")&"</c></p>","//c"))=SORT(FILTERXML("<p><c>"&SUBSTITUTE(B2,",","</c><c>")&"</c></p>","//c")))),"Match","Unmatch")

 

[bobbyexcel]

yes @Peter_SSs for the formula you mentioned in Col D, I want to include the same in vba code. could you please help me on that.

    Dim lastrownew2 As Integer
    lastrownew2 = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
   
    For Each c In Range("D2:D" & lastrownew2)
        c.Value = "=IF(PRODUCT(--(SORT(FILTERXML("<p><c>"&SUBSTITUTE(A2,",","</c><c>")&"</c></p>","//c"))=SORT(FILTERXML("<p><c>"&SUBSTITUTE(B2,",","</c><c>")&"</c></p>","//c")))),"Match","Unmatch")"

        c.Formula = c.Value
    Next

 

[DanteAmor]

This working as expected but how do I incorporate into VBA code. It says expected end of statement.

Sub WithFormula()
  Dim lr As Long
  lr = Range("B" & Rows.Count).End(3).Row
  With Range("C2")
    .FormulaArray = "=IF(SUM(--ISNUMBER(SEARCH("",""&TRIM(MID(SUBSTITUTE(B2, "","", REPT("" "",255))" & _
        ",255*(ROW($1:$2)-1)+1,255))&"","","",""&TRIM(SUBSTITUTE(A2,"", "","",""))&"","")))=2,""Match"", ""UnMatch"")"
    If lr > 2 Then .Copy Range("C3:C" & lr)
    .Resize(lr - 1).Value = .Resize(lr - 1).Value
  End With
End Sub