[Solved] Matching Text

[mfaisal.ce]

Dears,

I need to find out how i can compare two Cells having different strings to give result which is common in both;
like

A2: 05-08,16-17
B2: 00-13,16-17,22-23

Common in both cells: 16-17

How it is possible pragmatically? Kindly help

Regards,
Muhammad Faisal

 

[Yousuf Shovon]

How it is possible pragmatically?

Dear Faisal,

I understand you wish to compare two Cells having different strings to give a result that is common in both. To do so, use the split function in a macro to separate out the elements from both cells then loop through both sets to compare for equality. Although your question seems incomplete- What if there is more than one match? Or a partial match e.g. just 05?

For now, I have solved this as I understood using the following VBA:

Sub Cell_Comparison()
    Dim cellA As Range, cellB As Range
    Dim elementsA() As String, elementsB() As String
    Dim commonElements As String
    Dim elementA As Variant, elementB As Variant
    Dim isCommon As Boolean
    
    Set cellA = Range("A2")
    Set cellB = Range("B2")
    
    elementsA = Split(cellA.Value, ",")
    elementsB = Split(cellB.Value, ",")
 
    commonElements = ""
     
    For Each elementA In elementsA
        isCommon = False
        For Each elementB In elementsB
            If Trim(elementA) = Trim(elementB) Then
                isCommon = True
                Exit For
            End If
        Next elementB
       
        If isCommon Then
            commonElements = commonElements & Trim(elementA) & "-"
        End If
    Next elementA
   
    If Len(commonElements) > 0 Then
        commonElements = Left(commonElements, Len(commonElements) - 1)
       
        MsgBox "Common in Both Cells: " & commonElements
    Else
    
        MsgBox "No Common Elements Found"
    End If
End Sub

Try this and let us know if it works for you. Also, I am attaching the Excel file here.

Best Regards,
Yousuf Shovon