asked on

Excel, Coordinates, X,Y calc location of second column of X,Ys

I have four columns of data. Two sets of X,Y coordinates.
X1      Y1             X2        Y2
2893485.397      2159991.701            2893478.343      2159954.687
2893851.974      2159972.248            2893820.917      2159944.458
2894429.356      2159998.628            2894424.336      2159960.635
2895089.535      2159907.211            2895087.666      2159875.504
2895702.349      2159973.445            2895668.207      2159939.631
2896055.331      2159914.853            2896052.252      2159876.48
2896429.074      2160012.72            2896427.449      2159981.793
2896425.664      2160285.545            2896416.933      2160271.983
2896423.336      2160592.846            2896400.906      2160577.979
2893000.438      2160001.187            2892982.095      2159987.436

There's a total of 1200 rows.

I need to know if, for each item described by X1,Y1 there is an item described by X2,Y2 with 30 units in either direction. Not just the item in the corresponding row, but ANY item. i.e. within +/- 30 units in the X direction and the Y direction.

This is a bit tricky.

hgj1357

ASKER

A count of the matching items would be good. Although a zero for none and 1 for not zero would work. I'm more interested in the cases where there is no match.

Kyle Abrahams, PMP

I used this as a basis for the forumla:
http://www.mrexcel.com/forum/excel-questions/30130-count-if-value-falls-range.html

basically find if there's at least one X in range
find if there's at least one Y in range
if there's an X and Y in range, return 1, else 0.

Formula:

=IF(AND(COUNTIF(D:D,">="&A1-30)-COUNTIF(D:D,">="&A1+30)>0,COUNTIF(E:E,">="&B1-30)-COUNTIF(E:E,">="&B1+30)>0), 1,0)

Open in new window

Where:
A1 is your X1
B1 is your Y1
D:D is your X2 range
E:E is your Y2 Range

Will return 1 for true 0 for false.

Glenn Ray

This formula works (assuming X1,Y1,X2,Y2 in columns A,B,C,D). Add this in row 2 of any adjacent column:

=SUMPRODUCT(--(A2>=($C$2:$C$11)-30),--(A2<=($C$2:$C$11)+30),--(B2>=($D$2:$D$11)-30),--(B2<=($D$2:$D$11)+30))

The value returned is the number of X2/Y2 pairs that a given X1/Y1 pair matches.

See attached workbook for example and comparison with COUNTIF formulas.

Regards,
-Glenn
EE-CoordinateMatching-value-pair.xlsx

hgj1357

ASKER

@Kyle Abrahams

this works great, with one caveat. There are more items in columns A&B than in columns C&D

I should have mentioned this in the question. Sorry

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hgj1357

ASKER

Modifying Kyle's formula:

=IF(AND(COUNTIF(E$2:E$1089,">="&B1103-30)-COUNTIF(E$2:E$1089,">"&B1103+30)>0,COUNTIF(F$2:F$1089,">="&C1103-30)-COUNTIF(F$2:F$1089,">"&C1103+30)>0), 1,0)

as my X1,Y1 rows extend to 1089 and X2,Y2 rows to 1000

hgj1357

ASKER

I've requested that this question be closed as follows:

Accepted answer: 500 points for ged325's comment #a40125894
Assisted answer: 0 points for hgj1357's comment #a40127668

for the following reason:

I modified Kyle's formula a bit, but it works a charm,

SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hgj1357

ASKER

I am checking this out in ACAD as we speak. You might have a point Glenn!

Kyle Abrahams, PMP

Actually Glenn is correct in that the following returns an incorrect match:

try
10, 10 as your point

and then X2 points as
30 100
100 30

and it'll return true that there exists a match.

Glenn Ray

I had to do a similar analysis with asteroid detection and this is similar to the algorithm we used, except in reverse (detection of points with delta-x, delta-y greater than a set amount). It also had to calculate absolute differences (i.e., diagonals).

hgj1357

ASKER

Yep. Glenn is right. In the main, Kyle's formula worked, but not always. Glenn's appears to be spot on.

Sorry Kyle. Glenn takes the spoils.

hgj1357

ASKER

Battle tested!!

Glenn Ray

Thanks for recognizing this. If you end up needing a more-refined analysis (i.e., points within a 30-unit radius), let us know. That will be challenging.

Regards,
-Glenn

hgj1357

ASKER

Nah. I'm using this to see how many hydrants or not in a model, where our survey data says there is a hydrant. 30' was arbitrary, so square or radius is not critical. Nice solution!