Thread: Find Identical 4 numbers Straight Or Permuted In a Data Range

Let say we have this range

Column A
1234

2345

7891

1234

6501

and so on

Column B

4321

5584

5016

5231

2345

and so on

I just want a formula that can identify if number in column A is contained column B either in straight or permuted way

Example

1234 is in column B as 4321
2345 is in column B as 2345
6501 is in column B as 5016

Thank You

Try this user defined function.

Function FINDSPECIAL(Range1 As Range, Range2 As Range) As Boolean
Dim Range1Array() As String
Dim Range2Array() As String
Dim TempRange As String
Dim N As Integer
Dim M As Integer
Dim Range1Len As Integer
Dim Cell As Range

Range1Len = Len(Range1)

ReDim Range1Array(Range1Len - 1)
For N = 1 To Range1Len
    Range1Array(N - 1) = Mid(Range1.Value, N, 1)
Next N

'Bubble sort
For N = 0 To Range1Len - 1
    For M = 0 To Range1Len - 1
        If Asc(Range1Array(N)) > Asc(Range1Array(M)) Then
            TempRange = Range1Array(N)
            Range1Array(N) = Range1Array(M)
            Range1Array(M) = TempRange
        End If
    Next M
Next N

For Each Cell In Range2
    If Len(Cell) = Range1Len Then
    
         ReDim Range2Array(Range1Len - 1)
         
         For N = 1 To Range1Len
             Range2Array(N - 1) = Mid(Cell.Value, N, 1)
         Next N
         
   'Bubble sort
         
         For N = 0 To Range1Len - 1
             For M = 0 To Range1Len - 1
                 If Asc(Range2Array(N)) > Asc(Range2Array(M)) Then
                     TempRange = Range2Array(N)
                     Range2Array(N) = Range2Array(M)
                     Range2Array(M) = TempRange
                 End If
             Next M
         Next N
         
         FINDSPECIAL = True
         For N = 0 To Range1Len - 1
             If Range1Array(N) <> Range2Array(N) Then
                 FINDSPECIAL = False
                 Exit For
             End If
         
         Next N
    End If
    If FINDSPECIAL = True Then Exit Function
Next Cell

End Function

Paste this into a new module in the VBA editor (alt F11).

In the original sheet, type in =FINDSPECIAL(A1,$B$1:$B$5) into C1 and copy down.

Also, if you dealing only with 4 digit numbers, with a formula.

=SUMPRODUCT(ISNUMBER(SEARCH(MID(A1,1,1),$B$1:$B$5))*ISNUMBER(SEARCH(MID(A1,2,1),$B$1:$B$5))*ISNUMBER(SEARCH(MID(A1,3,1),$B$1:$B$5))*ISNUMBER(SEARCH(MID(A1,4,1),$B$1:$B$5)))