Excel Help

J

JimmyRott

Expert Member
Joined
Feb 9, 2014
Messages
1,195
Reaction score
127
I've got XYZ coordinates of two surveys that were done on the same area. However the two surveys were not performed to the same datum level. I am trying to find communal points between the two data sets and compare the elevation values in order to normalise the data and combine the two surveys into one.

Below is a screenshot of my spreadsheet:

Survey XYZ.jpg

So, basically what I want to do is look for duplicate values in columns A and F. I then want to compare the corresponding values in columns B and G and identify points that have both the same X and Y coordinates. Any hints on how to do this?

Unfortunately the duplicate points will not fall in the same row. So I will need to compare all values in column F to all values in column A.
 
JimmyRott said:
I've got XYZ coordinates of two surveys that were done on the same area. However the two surveys were not performed to the same datum level. I am trying to find communal points between the two data sets and compare the elevation values in order to normalise the data and combine the two surveys into one.

Below is a screenshot of my spreadsheet:

View attachment 204178

So, basically what I want to do is look for duplicate values in columns A and F. I then want to compare the corresponding values in columns B and G and identify points that have both the same X and Y coordinates. Any hints on how to do this?

Unfortunately the duplicate points will not fall in the same row. So I will need to compare all values in column F to all values in column A.
Click to expand...

Explain what you mean by the bold above?
 
SeRpEnT said:
Explain what you mean by the bold above?
Click to expand...

The data set on the left is from the first survey, the data set on the right is from the second survey. Column A contains X coordinates, column B contains Y coordinates and column C contains Z coordinates. Similarly in the second data set column F contains X coordinates, column G contains Y coordinates and column H contains Z coordinates.

I've been told that there are 4 points that appear in both data sets (with differing Z values). I am trying to identify these points.
 
Last edited:
JimmyRott said:
The data set on the left is from the first survey, the data set on the right is from the second survey. Column A contains X coordinates, column B contains Y coordinates and column C contains Z coordinates. Similarly in the second data set column F contains X coordinates, column G contains Y coordinates and column H contains Z coordinates.

I've been told that there are 4 points that share the same X and Y coordinates and appear in both data sets. I am trying to identify these points.
Click to expand...

It'll probably take me less than a minute in SAS and any SQL developer should be able to do the same. Why not call in a favour from a friend?
 
So as an example if there are two points that have coordinates 100,100,5 and 100,100,10 i know that the datum of the two surveys differs by 5m and I can alter either data set accordingly.
 
JimmyRott said:
The data set on the left is from the first survey, the data set on the right is from the second survey. Column A contains X coordinates, column B contains Y coordinates and column C contains Z coordinates. Similarly in the second data set column F contains X coordinates, column G contains Y coordinates and column H contains Z coordinates.

I've been told that there are 4 points that appear in both data sets (with differing Z values). I am trying to identify these points.
Click to expand...

I can think of a another formula to do it. Not fancy but it will work. Try @SUMIFS.
 
Bar0n said:
It'll probably take me less than a minute in SAS and any SQL developer should be able to do the same. Why not call in a favour from a friend?
Click to expand...

I don't know any SAS or SQL developers. I'm in an office with 5 engineers and no one has a clue how to do this. The one guy I suspect might know is on leave till next week. I'll probably just end up stalling with the client and pass it off to him when he gets back.
 
I'm not sure if I understand you correct here. Do you just want to search through a huge amount of data and compare Data from the right hand 3 columns to data from the left hand 3 columns and find the 4 instances where the values of column A and F are the same and also for B and G.
 
JimmyRott said:
I don't know any SAS or SQL developers. I'm in an office with 5 engineers and no one has a clue how to do this. The one guy I suspect might know is on leave till next week. I'll probably just end up stalling with the client and pass it off to him when he gets back.
Click to expand...

Send me a link to the file. I'll give you the points.
 
In column D enter this formula next to the first row where you want to start looking:
=SUMIFS($F$1:$F$12,$F$1:$F$12,$A$1:$A$12,$G$1:$G$12,$B$1:$B$12)
Just replace the 12 with the last row number in your case.
Then copy down column D all the way to the last row you want to search.

In column I enter this formula next to the first row where you want to start looking:
=SUMIFS($A$1:$A$12,$A$1:$A$12,$F$1:$F$12,$B$1:$B$12,$G$1:$G$12)
Just replace the 12 with the last row number in your case.
Then copy down column I all the way to the last row you want to search.

This formula will then add up the contents (not really of much use here) wherever it finds duplicate values between A&F and also B&G.
So you only then need to look down the columns and finds the cells in D and I where the value is not zero (in other words the sum formula was triggered).

Not a very elegant method but it works.
 
It will be a very manual process, but I am sure you can do it with MATCH or even LOOKUP, then just find the value where the function returns a 1.
 
SeRpEnT said:
In column D enter this formula next to the first row where you want to start looking:
=SUMIFS($F$1:$F$12,$F$1:$F$12,$A$1:$A$12,$G$1:$G$12,$B$1:$B$12)
Just replace the 12 with the last row number in your case.
Then copy down column D all the way to the last row you want to search.

In column I enter this formula next to the first row where you want to start looking:
=SUMIFS($A$1:$A$12,$A$1:$A$12,$F$1:$F$12,$B$1:$B$12,$G$1:$G$12)
Just replace the 12 with the last row number in your case.
Then copy down column I all the way to the last row you want to search.

This formula will then add up the contents (not really of much use here) wherever it finds duplicate values between A&F and also B&G.
So you only then need to look down the columns and finds the cells in D and I where the value is not zero (in other words the sum formula was triggered).

Not a very elegant method but it works.
Click to expand...

Thanks for the reply, but there seems to be an error in the formula. I'm reading up about SUMIFS and seeing if I can fix it.
 
I cannot find points where A = F and B = G. Here are the points where A = F:

6fc44a2789_jimmyrott1.PNG


and here are the points where B = G:

b9849d8d0a_jimmyrott2.PNG
 
Bar0n said:
I cannot find points where A = F and B = G. Here are the points where A = F:

6fc44a2789_jimmyrott1.PNG


and here are the points where B = G:

b9849d8d0a_jimmyrott2.PNG
Click to expand...

Ok, thanks a lot. There must be some more errors in the survey then.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X