Finding Same Point in 3D Space and Sorting Next to Each Other Based on Their Coordinates

Hello All,

I have a list of coordinates in 3D space (X,Y,Z) of a couple thousand points. These points represent the midpoint of a line in 3D space. Ideally, two points would be exactly the same (due to two lines intersecting in 3D space at their midpoint) so if my data contained 2000 points, only 1000 would be unique. However, this is not the case and the two points that should be identical are simply very close to each other. The data that I have is scrambled. What I would like to do is sort my data so that two points that should be the same are next to each other. Attached is a sample file of what I have and what I would like. I also showed an example of sorting by one criteria only (one axis) but how that can lead to erroneous results. I've been wrecking my brain for a while trying to figure this one out and would greatly appreciate some input.

Thank you for reading!

MrShorty, that is a great question that I'm not entirely sure. I actually did create an algorithm based on distance but that also provided some erroneous results. For instance, the distance from 0,0,0 to 3,3,3 is exactly the same as the distance to -3,-3,-3. So if I was to sort by distance, my sorting would then tell me that those two points are next to each other when in fact they are not. I was thinking I may need to write some piece of code (in another program that I'm more familiar with its coding syntax) in order to do what I want. However, I was hoping there was a tool in Excel that would help me sort by multiple criteria simultaneously as opposed to one at a time.


kvsrinivasamurthy, I care that 1 is near to 5, 4 is near to 2, and 6 is near to 3 because those pairs should be the exact same point in 3D space. I'm not really concerned that 2 should be next to 1 or 3 next to 4 in the sorting because those are two far away points in 3D space. I hope this clarified things but let me know if it didn't.

MrShorty, I misread part of your response. Your suggestion was to calculate the distance between each possible pair of points and then do the sorting. That's what I'm thinking of doing now as the approach that I described previously did not work for 100% of the data. To be honest, I don't know how to do that in Excel but I may be able to figure it out in MathCad.