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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]4215[/TD]
[TD="align: right"]5142[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=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}))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]{=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)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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!