Forum Discussion

Grosso1280's avatar
Grosso1280
Copper Contributor
Oct 30, 2020

Formula for for writing common numbers

What formula will help me write common numbers from 2 cells? In cell A1 I have: 1,2,3. In cell B1 I have: 2,3,4. I would like cell C1 to write to me: 2 and 3.

excel
Formulas and Functions

4 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 30, 2020

    Grosso1280 

    With formula that could be

    =TEXTJOIN(",",1,
  XLOOKUP(
     FILTERXML("<r><n>" & SUBSTITUTE(A1,",","</n><n>") & "</n></r>",  "//n"),
     FILTERXML("<r><n>" & SUBSTITUTE(B1,",","</n><n>") & "</n></r>",  "//n"),
     FILTERXML("<r><n>" & SUBSTITUTE(B1,",","</n><n>") & "</n></r>",  "//n"),
""))
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 30, 2020

    Grosso1280 

    Here is a custom VBA function:

    Function Common(ByVal s1 As String, ByVal s2 As String) As String
    Dim a() As String
    Dim i As Long
    Dim r As String
    s1 = Replace(s1, " ", "")
    s2 = "," & Replace(s2, " ", "") & ","
    a = Split(s1, ",")
    For i = 0 To UBound(a)
        If InStr(s2, "," & a(i) & ",") Then
            r = r & "," & a(i)
        End If
    Next i
    If r <> "" Then
        Common = Mid(r, 2)
    End If
End Function

    Use like this:

    =Common(A1,B1)

      • mtarler's avatar
        mtarler
        Silver Contributor
        Oct 30, 2020

        Grosso1280 alternatively this formula should work (but it does return a blank because I reference the entire column instead of only the data set range):

         

        =IFERROR(SORT(INDEX(B:B,UNIQUE(MATCH(A:A,B:B,0)))),"")

         

Resources