Coordinates Rotate and Translate

I have a big list of coordinates, format (name,x,y,z), maybe a thousand long. Also it have a second list of coordinates that between 2 and 6 long, that are relative to each other, distances and angles locked.

I want excel to compare my small group to the big list finding the closest matching set and translate and rotate the small set onto the new coordinates. I wanted to use the least squares method to find a match and keep a record of the tolerances of the match.

genesis??

Well I'm a surveyor, and I'm trying to improve my own skill-set, this is purely a stand alone problem that i posed myself. If i find an elegant solution I'll build upon it, for example last week I got excel to Reduce a .gsi file (csv containing horizontal angle,vertical angle, slope distances) format down to the Easting,Northing,Elevation corrected for Temp and Pressure. This is the next logical challenge. If i know where the control points need to be moved, then i can move the whole data set with it.

Your understanding of the question is spot on, might even be better than my description. If i where programming this myself I'd get get the distance between each point in the small group and every other point in the small group, storing the answers. The starting on the the big list calculating the distances between each and every other point, and compare them to my stored distances, keeping note of the points that where within a set tolerance.This would filter the data down, allow for a second process to check all the angel and compare in the same way. Hope that makes sense.

I know excel has all these add-ins like the solver and I'm eager to learn more mathematical robust ways of doing things

About Z, meaning x any y would change

I'll create some test data and upload, making sure to have a identical set of points in full list

It would be few translated it the many, and they are actually the same real world survey station observed for a different location, meaning the distances and angles between them remains the same

testdata.xlsx

I've added a simple workbook with a large list of unique coordinates. and 6 other coordinates that are the result of a selection being rotated and translated out of place. Apologizes in the delay getting this done, had work needed doing.

I'll put a few hours into this tomorrow evening, to see if I can get anywhere with it.

What i did was copy 6 coordinates, and rotated them about the origin, so there x,y values changed, then the translated them in both x and y also.

The z values i left alone, so their identical to the original.

Any x,y coordinates can be used as the center of rotation.

yes , i checked out distances,internal and external angles.