Match Index with Tolerances
Match Index with Tolerances
(OP)
Hi,
I'd like to set up a function that compares the locations in the left box with the Master locations in the right box. The function will return the Master location if a match is found.
I've managed to break down the locations by X and Y coordinates.
The test would be something like IF Dist < Tolerance THEN Returns corresponding master location. The function would test all of the master locations.
Dist = SQRT ( |X1-X2|^2 + |Y1-Y2|^2 )

Any help would be much appreciated.
I'd like to set up a function that compares the locations in the left box with the Master locations in the right box. The function will return the Master location if a match is found.
I've managed to break down the locations by X and Y coordinates.
The test would be something like IF Dist < Tolerance THEN Returns corresponding master location. The function would test all of the master locations.
Dist = SQRT ( |X1-X2|^2 + |Y1-Y2|^2 )

Any help would be much appreciated.





RE: Match Index with Tolerances
Assumptions:
A B C D E MASTER tol 0.5 x y x2 y2 mtch 2.683 19.24 2.437 19.16 2.683:19.24 3.043 22.16 3.015 22.07 3.043:22.16 4.998 13.35 4.998 13.26 4.998:13.35The ranges in MASTER are named x & y
The funcion to calculate mtch, in a standard module
CODE
Function mtch(x2 As Single, y2 As Single, tol As Single) Dim rx As Range Dim x1 As Single, y1 As Single Dim dist As Single 'Dist = SQRT ( |X1-X2|^2 + |Y1-Y2|^2 ) For Each rx In [x] x1 = rx.Value y1 = rx.Offset(0, 1).Value dist = ((x1 - x2) ^ 2 + (y1 - y2) ^ 2) ^ 0.5 If dist < tol Then Exit For Next mtch = x1 & ":" & y1 End FunctionUse the mtch function as you would any other function...
E12: =mtch(C12,D12,tol)
Skip,
for a NUance!
RE: Match Index with Tolerances
However, I'm getting a #VALUE error when attempting to replicate it. Do you need to add into the function code something that defines the 'tol'?
RE: Match Index with Tolerances
Making prolific use of Named Ranges is a wise and beneficial practice.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Match Index with Tolerances
RE: Match Index with Tolerances
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Match Index with Tolerances
It would be pretty easy to extend the code given to return all matches as an array which is then written to the sheet. Or alternatively return the point with the least distance.
RE: Match Index with Tolerances
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Match Index with Tolerances
Got it to work fine, that's for the help SV.