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

 

[bobbyexcel]

@DanteAmor this code isn't working if I have the data like the below..

Book2
	A	B	C	D
1	Data 1	Data 2	Result 1	Result 2
2	S,3		UnMatch	#VALUE!
3	S		UnMatch	#VALUE!
4	3,S		UnMatch	#VALUE!
5	3,S	S,3	Match	Match
6	S,3	3,S	Match	Match
7	S,3	S,3	Match	Match
8	S	S	UnMatch	Match
9	S	S, S	Match	Match
Sheet2

Cell Formulas
Range	Formula
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")

but D Column formula is working..

so I'm modifying the same with conditional check.. here is the code I've tried but your code not working in first condition(if length of both matches & >2) .. could you please help..

Correction: my data is in E,F and result in G.. here is the code

  Dim lr As Long
 
    lr = Range("B" & Rows.Count).End(3).Row

 For Each c In Range("E2:E" & lr)
 heck = c.Row
' check = Len(Range("E" & c.Row))
    If (Len(Range("E" & c.Row)) > 1 And Len(Range("F" & c.Row)) > 1) Then
        With Range("G" & c.Row)
            .FormulaArray = "=IF(SUM(--ISNUMBER(SEARCH("",""&TRIM(MID(SUBSTITUTE(F & c.Row, "","", REPT("" "",255))" & _
                ",255*(ROW($1:$2)-1)+1,255))&"","","",""&TRIM(SUBSTITUTE(E & c.Row,"", "","",""))&"","")))=2,""Match"", ""UnMatch"")"
          '  If lr > 2 Then .Copy Range("H3:H" & lr)
           ' .Resize(lr - 1).Value = .Resize(lr - 1).Value
        End With
        GoTo NextIteration
    End If
  
    If (Len(Range("E" & c.Row)) = 1 And Len(Range("F" & c.Row)) = 1) Then
        If (UCase(Range("E" & c.Row)) = UCase(Range("F" & c.Row))) Then
            Range("G" & c.Row) = "Match"
            GoTo NextIteration
        End If
  
    Else
        Range("G" & c.Row) = "UnMatch"
    End If
  
NextIteration:
 
  Next

 

[Rick Rothstein]

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

Representative samples matter! If your data are actually two single characters separated by a comma (no space), then the code for that is simpler than if your text is longer.

Sub MatchUnmatch()
  Dim Cell As Range
  For Each Cell In Range("A2", Cells(Rows.Count, "A").End(xlUp))
    If Cell.Value = StrReverse(Cell.Offset(, 1).Value) Then
      Cell.Offset(, 2).Value = "Match"
    Else
      Cell.Offset(, 2).Value = "Unmatch"
    End If
  Next
End Sub

 

[DanteAmor]

this code isn't working if I have the data like the below..

That is exactly what you were asked in a previous post, if you have other surprises. In your examples you didn't put empty cells. You must put all possible cases.

Correction: my data is in E,F and result in G.. here is the code

But now you have new data:

varios 23jun2022 rango con indice.xlsm
	A	B
1	Data 1	Data 2
2	S,3
3	S
4	S	S
5	S	S, S
Hoja1

Different from your OP.

 

[bobbyexcel]

I've modified my script like this with both of your codes and it worked flawlessly. Thanks @Rick Rothstein for the last minute savior .. @DanteAmor You are my complete path maker on this..

'    Dim lr As Long
'    Dim Cell As Range
  
    lr = Range("B" & Rows.Count).End(3).Row

    For Each c In Range("E2:E" & lr)
 
        If (Len(Range("E" & c.Row)) > 1 And Len(Range("F" & c.Row)) > 1) Then
            If LCase(c.Value) = LCase(StrReverse(c.Offset(, 1).Value)) Or LCase(c.Value) = LCase(c.Offset(, 1).Value) Then
                c.Offset(, 2).Value = "Match"
            Else
                c.Offset(, 2).Value = "Unmatch"
            End If
        GoTo NextIteration3
        End If
    
        If (Len(Range("E" & c.Row)) = 1 And Len(Range("F" & c.Row)) = 1) Then
            If (UCase(Range("E" & c.Row)) = UCase(Range("F" & c.Row))) Then
                Range("G" & c.Row) = "Match"
                GoTo NextIteration3
            End If
        Else
            Range("G" & c.Row) = "UnMatch"
        End If
    
NextIteration3:
  
  Next

 

[Peter_SSs]

I've modified my script like this with both of your codes and it worked flawlessly.

.. then wouldn't this do the same job?

Sub Test()
  Dim c As Range

  For Each c In Range("E2:E" & Range("B" & Rows.Count).End(3).Row)
    c.Offset(, 2).Value = IIf(Len(c.Value) > 0 And (LCase(c.Value) = LCase(c.Offset(, 1).Value) Or LCase(c.Value) = StrReverse(LCase(c.Offset(, 1).Value))), "Match", "Unmatch")
  Next c
End Sub

 

[smozgur]

Almost all expert replies in this question answered one of the questions that transformed to another question with different samples sent in different posts.

However, as it is also confirmed in post #6 (even failing due to the VBA implementation mistake which is nothing to do with the original question and the provided answer), post #4 clearly solves the question by also considering the second data set provided (and post #10 fulfills the VBA request with the solution formula).

As mentioned above, there are also other answers that solved the question with changed data sets and constraints including the OP's own post that was initially marked as the answer, however, to help future readers who are seeking a solution for the question asked in the original post, the marked solution has been switched accordingly.

@bobbyexcel - In your future questions, please "State your question clearly, including your entire need at the start." as it is mentioned in Guidelines 5-a.