Formula for for writing common numbers

%3CLINGO-SUB%20id%3D%22lingo-sub-1836760%22%20slang%3D%22en-US%22%3EFormula%20for%20for%20writing%20common%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1836760%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EWhat%20formula%20will%20help%20me%20write%20common%20numbers%20from%202%20cells%3F%20In%20cell%20A1%20I%20have%3A%201%2C2%2C3.%20In%20cell%20B1%20I%20have%3A%202%2C3%2C4.%20I%20would%20like%20cell%20C1%20to%20write%20to%20me%3A%202%20and%203.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1836760%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1837006%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20for%20writing%20common%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1837006%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F847423%22%20target%3D%22_blank%22%3E%40Grosso1280%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20custom%20VBA%20function%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EFunction%20Common(ByVal%20s1%20As%20String%2C%20ByVal%20s2%20As%20String)%20As%20String%0A%20%20%20%20Dim%20a()%20As%20String%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20Dim%20r%20As%20String%0A%20%20%20%20s1%20%3D%20Replace(s1%2C%20%22%20%22%2C%20%22%22)%0A%20%20%20%20s2%20%3D%20%22%2C%22%20%26amp%3B%20Replace(s2%2C%20%22%20%22%2C%20%22%22)%20%26amp%3B%20%22%2C%22%0A%20%20%20%20a%20%3D%20Split(s1%2C%20%22%2C%22)%0A%20%20%20%20For%20i%20%3D%200%20To%20UBound(a)%0A%20%20%20%20%20%20%20%20If%20InStr(s2%2C%20%22%2C%22%20%26amp%3B%20a(i)%20%26amp%3B%20%22%2C%22)%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20r%20%3D%20r%20%26amp%3B%20%22%2C%22%20%26amp%3B%20a(i)%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20i%0A%20%20%20%20If%20r%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20Common%20%3D%20Mid(r%2C%202)%0A%20%20%20%20End%20If%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EUse%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%3DCommon(A1%2CB1)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1837239%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20for%20writing%20common%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1837239%22%20slang%3D%22en-US%22%3EThanks%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

4 Replies

@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)

Thanks

@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)))),"")

 

@Grosso1280 

With formula that could be

image.png

=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"),
""))