Match two sets of numbers regardless of order

[wcthrill]

Hi

Im trying to figure how Excel can match a set of numbers of the position.
So for example I would like Excel to provide a true result of the two below numbers
as they both contain the same numbers but in a different order

4215 5142

 

[JLGWhiz]

This can probably be done with a formula, but here is some vba in case you want to use it.

Sub t()
Dim i As Long
a = 4215
b = 5142
For i = 1 To 4
    If InStr(b, Mid(a, i, 1)) = 0 Then
        x = x + 1
    End If
Next
    If x > 0 Then
        MsgBox "No Match"
    Else
        MsgBox "Matches"
    End If
End Sub

 

[Tetra201]

Withdrawn

 

[Tetra201]

The following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) works for the posted example (yet needs more testing):

=SUM(IF(ISNUMBER(FIND((ROW($1:$10)-1)&"",A1)),ROW($1:$10)^ROW($1:$10)))+LEN(A1)=SUM(IF(ISNUMBER(FIND((ROW($1:$10)-1)&"",B1)),ROW($1:$10)^ROW($1:$10)))+LEN(B1)

 

[Eric W]

One formula option:

	A	B	C	D
1	4215	5142	TRUE	TRUE

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula C1 =AND(SMALL(MID(A1,{1,2,3,4},1)+0,{1,2,3,4})=SMALL(MID(B1,{1,2,3,4},1)+0,{1,2,3,4})) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D1 {=IFERROR(AND(SMALL(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0,ROW(INDIRECT("1:"&LEN(A1))))=SMALL(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)+0,ROW(INDIRECT("1:"&LEN(B1))))),FALSE)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The formula in C1 works for 2 4-digit numbers. Not too terrible. The formula in D1 works for 2 numbers of arbitrary size (a bit messier!) Both only work on numbers, no letters.

 

[wcthrill]

One formula option:

Eric, thanks for the quick reply!