How to find duplicate digits on Excel.

[trident0911]

Hi Guys, I would like to know whether if its possible to identify the same 4 digits in Excel?

Example : Number 1234, to find other combinations of these four Digits like 1243, 1342, 4213 etc.

Data will also contain repeating numbers like 1113 and 2244.

Your solutions will be much appreciated, thanks in advance!

 

[Anbuselvam]

As per your explanation, it is just an idea to find the duplicates by summing up the cell values.

Example to find duplicate logic is

1234 (1+2+3+4 = 10)
4231 (4+2+3+1 = 10)
Likewise, you can find the duplicates.

To do the same,
Add the below formula in cell B1 then drag it down till the end of the data rows.

=SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

 

[offthelip]

I haven't got time to complete the code but here is some code that will extact the 4 digits into an array, which is the way I would start. The next step would then be to loop down the column and do a comparison. Not necessarily that easy because of the requirement that they can be in any order:

Sub test()
tt = 1234
Dim ta(1 To 4) As Integer
 For i = 1 To 4
   ta(i) = tt Mod 10
   tt = WorksheetFunction.RoundDown(tt / 10, 0)
  
 Next i
MsgBox (ta(1) & " " & ta(2) & " " & ta(3) & " " & ta(4))


End Sub

 

[bebo021999]

As per your explanation, it is just an idea to find the duplicates by summing up the cell values.

Example to find duplicate logic is

1234 (1+2+3+4 = 10)
4231 (4+2+3+1 = 10)
Likewise, you can find the duplicates.

To do the same,
Add the below formula in cell B1 then drag it down till the end of the data rows.

=SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

How about:
1234 & 1135? Same total (10) but not dupplicate

 

[mohadin]

Hi
Have a look to this code

Sub testy()
    Dim Col As Collection
    a = Cells(1, 1).Resize(10)
    With CreateObject("scripting.dictionary")
        For i = 1 To 10
            Set AL = CreateObject("System.Collections.ArrayList")
            For ii = 1 To 4
                AL.Add Mid(a(i, 1), ii, 1)
            Next
            AL.Sort
            X = Join(AL.toarray, "")
            If Not .exists(X) Then
                .Add X, a(i, 1)
            Else
                .Item(X) = .Item(X) & "/" & a(i, 1)
            End If
            Set AL = Nothing
        Next
       For i = 0 To .Count - 1
       X = Split(.Items()(i), "/")
       Cells(1, i + 5).Resize(UBound(X) + 1) = Application.Transpose(X)
       Next
    End With
End Sub

 

[Anbuselvam]

possible to identify the same 4 digits in Excel?

You were asked the same 4 digits?

you can use my solution if it is the same 4 digits. otherwise go with VBA.