Find duplicate numbers within cells

[rup1234]

I have a table with multiple values in the same cell separated by commas.
I also have duplicate values within the cell.

A1- 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19
B1-19,2,1,0,7,8,9,5,13,20,21,22,23,24,66,77,11,12,14,44,32,26,27

now i want duplicate numbers from both the cell

output-19,2,1,0,7,8,9,5,13,11,12,14,

 

[MickG]

Try this for results in "A3".

[COLOR="Navy"]Sub[/COLOR] MG20Sep25
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nstr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1:B1")
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    sp = Split(Dn.Value, ",")
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(sp)
        [COLOR="Navy"]If[/COLOR] Not .Exists(Trim(sp(n))) [COLOR="Navy"]Then[/COLOR]
             .Add Trim(sp(n)), Trim(sp(n))
        [COLOR="Navy"]Else[/COLOR]
            nstr = nstr & IIf(nstr = "", Trim(sp(n)), ", " & Trim(sp(n)))
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR]
Range("A3") = nstr
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[rup1234]

Thankyou so much but if i compare three rows than it does not work

 

[Peter_SSs]

Welcome to the MrExcel board!

If you have the TEXTJOIN function (Excel through Office 365) then try this worksheet formula. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

Excel Workbook
	A	B	C
1	0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19	19,2,1,0,7,8,9,5,13,20,21,22,23,24,66,77,11,12,14,44,32,26,27	19,2,1,0,7,8,9,5,13,11,12,14
Dupes

 

[MickG]

Try this for "A1 to A3" , alter range at top of code to suit.
This will return duplicates or multidupicate values in cell "A10"

[COLOR="Navy"]Sub[/COLOR] MG20Sep46
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nstr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, Fd [COLOR="Navy"]As[/COLOR] Boolean
Set Rng = Range("A1:A3") '[COLOR="Green"][B]Change range to suit !!![/B][/COLOR]
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Sp = Split(Dn.Value, ",")
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
        [COLOR="Navy"]If[/COLOR] Not .Exists(Trim(Sp(n))) [COLOR="Navy"]Then[/COLOR]
             .Add Trim(Sp(n)), Fd
        [COLOR="Navy"]Else[/COLOR]
           [COLOR="Navy"]If[/COLOR] .Item(Trim(Sp(n))) = False [COLOR="Navy"]Then[/COLOR]
                nstr = nstr & IIf(nstr = "", Trim(Sp(n)), ", " & Trim(Sp(n)))
                .Item(Trim(Sp(n))) = True
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR]
Range("A10") = nstr
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Peter_SSs]

Another option is a user-defined function. Here are two to consider. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Dupes(s1 As String, s2 As String) As String
  Dim m As Object
  
  With CreateObject("VBScript.regExp")
    .Global = True
    .Pattern = "\,(" & Replace(Replace(s1, " ", ""), ",", "|") & ")(?=\,)"
    For Each m In .Execute("," & Replace(s2, " ", "") & ",")
      Dupes = Dupes & m
    Next m
  End With
  Dupes = Mid(Dupes, 2)
End Function

Function ListDupes(s1 As String, s2 As String) As String
  Dim itm As Variant
  Dim s1Compact As String
  
  s1Compact = "," & Replace(s1, " ", "") & ","
  For Each itm In Split(Replace(s2, " ", ""), ",")
    If InStr(1, s1Compact, "," & itm & ",") Then ListDupes = ListDupes & "," & itm
  Next itm
  ListDupes = Mid(ListDupes, 2)
End Function

Excel Workbook
	A	B	C	D
1	0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19	19,2,1,0,7,8,9,5,13,20,21,22,23,24,66,77,11,12,14,44,32,26,27	19,2,1,0,7,8,9,5,13,11,12,14	19,2,1,0,7,8,9,5,13,11,12,14
2	3,2,1,5,67,32,44	1,2,3,4,5,6,7,8,9,10	1,2,3,5	1,2,3,5
Dupes (2)