Selected Answer
I have taken this as far as I can. Now it's your turn to check. The problem is that the code in the attached workbook doesn't produce the result you have indicated, to wit, Point 1 to match with points 3 and 93. My program gets 2, 83 and 88. But first the basics.
The procedure to run is called Sub Calculate_ZDistance. It is located in the code module Z_Distance which also has some supporting procedures. There is no code anywhere else and you can transfer the entire module by dragging it into your own project in the Project Explorer window while both workbooks, the attached and your own, are open. I presume that you know how to find the code module and run the procedure. If not, please ask.
There are a lot of comments in the code which I have added to enable you to adapt it to another workbook where rows and columns might be different. It is very easy to modify because I designed it to be easy. Please read the instructions in the code.
While doing so, please look for the part of the procedure Calculate_ZDistance which I have copied here.
' Disable one of the next two lines of code by prefixing it with an apostrophe:
' If Tmp <= NdcMax And Tmp >= NdcMin Then
If Tmp <= NdcMax Then
Tmp = Abs(ZData(NzdZ, R) - ZData(NzdZ, i))
Debug.Print "Row "; R; " - Row "; i; " = "; ZData(NzdZ, R); " - "; ZData(NzdZ, i), "Result = "; Tmp
If Tmp > Max(NmxMax) Then
Max(NmxMax) = Tmp
Max(NmxId) = i
End If
End If
End If
Next i
Stop
At the top of this excerpt it says to disable one of the first two lines of code. The first line demands a minimum AND a maximum value, the second one a maximum only. The two named variables NdxMin and NdcMax are defined at the top of the code sheet as 100 and 400 respectively. You can modify the values there. Currently the first line is disabled, meaning the code looks for a value < 400 only, without minimum.
The code line Debug.Print "Row "; R; " - Row "; i; " = "; ZData(NzdZ, R); " - "; ZData(NzdZ, i), "Result = "; Tmp prints a result to the Immediate Window as shown below.
Row 1 - Row 2 = 1.628 - -1.177 Result = 2.805
Row 1 - Row 83 = 1.628 - 5.625 Result = 3.997
Row 1 - Row 88 = 1.628 - -5.488 Result = 7.116
Each line represents one comparison with a value <400 found when comparing the first item ("Row 1") with rows 2, 83 and 88. The largest of these was selected and printed to E2, in this case 7.116 from row 88 which is written to F2.
At the bottom of the above excerpt there is a Stop command. Code execution will be halted to allow you to examine the result. Press F5 to continue with the next item. There are 95 rows in the sample. I don't know what to look for.
When you are done checking please disable the Debug.Print line and the Stop command, or delete the lines completely so that you can see the entire functionality. Let me know what errors you find and I'll fix them.
======================= Edit Jul 22, 2018
In order to question your data you need absolute confidence in my program - confidence I myself don't have. Therefore I have attached a second workbook (today's date in its name). All is identical with the first except that it contains extra code to output interim results. But first, please understand what you should be looking for.
My macro first extracts the relevant data from your Sheet1 and writes them into memory. This accounts for the speed of the program. Irrelevant data are counted and removed. Now you have three row IDs. There is the worksheet row, the Point ID and the array vector number. The code first works with the row number and later with the vector number. The Point ID is carried as as means of checking but the program doesn't (can't) check. Therefore this is what you should do before you doubt your data. Make sure that the program actually processes the data it thinks it processes. Of coruse I have checked this carefully. You should do it metitiously. The new additions to the code assist.
When you run the code a new worksheet will be appended called "Data". It contains a dump of the data first extracted for processing. Observe that the columns X, Y and (Z + ID) are extracted separately and later joined using their vector number. Its foolproof - but neither one of us is a fool; we find intelligent ways to go wrong. I have added a permanent copy of this dump to to the workbook in Sheet3. There I have added worksheet formulas which you might want to use for checking for discrepancies. But, as always, consider the possibility that the mistake isn't in the program but in the checking.
The program then goes on to check each of the items in the "Data" sheet against each other item in the "Data" sheet. (The worksheet isn't consulted anymore.) The new additions generate a worksheet for each item, giving it the name of each item's Point ID. Note that these sheets show the ID numbers of the items compared but the actual list was created using the underlying array's (ZData) vector numbers.
In both types of worksheets the columns have captions helping you to identify the data. The second type of sheet has the columns "in range" and "New Max". In range = True if the distance is <400 (with current macro settings). If the column shows False, the calculated distance isn't considered for the next check. The next check just determines the highest of the not eliminated measurements. At the start the highest is zero. Therefore the first result is a new maximum and the column shows True. Thereafter that value is compared with each subsequent measurement, top to bottom, and updated if a larger one is found. Therefore the last TRUE in the column is the final result.
I suggest that you check all the work the program does. If it works correctly - guaranteed by your scrutiny - the doubt your data. If you need more interim results let me know and I shall add them.