# Find any coordinates within +/- 0.25 of a given coordinate

1. ## Find any coordinates within +/- 0.25 of a given coordinate

Hi Guys,

Have a pretty good challenge that I could use your help with!

I have 2 tables of data, "Columns" and "Stacks". Each label in "Columns" is unique and has a corresponding entry in "Stacks". "Stacks also contains a Z and X coordinate for each label.

The data represent columns on different levels, some of which stack on top of each other, and some don't. A column that is stacked on another will have "_L2" or "_L3" suffix, depending on which level of the stack it is. If a column has "_L1" it is the first level of a stack.

However, the software I pull this data from will occasionally misplace a column when it should be stacking... ie, if column A_L1 is at (32,43), a column above will also be at (32,43), but labeled A_L2. The error occurs when a column is at (32.01,43) or maybe (32,39.91), etc... this results in B_L1, a completely different column even though the column should be stacking on top of A_L1.

So heres the challenge: For every entry in "Columns", I need to find its X and Z coordinates in "Stacks" (I have done this part already), and compare the X to all other X and the Z to all other Z coordinates. If this finds a coordinate in "Stacks" where both X and Z are within +/- 0.25, (but the coordinate is not the same as the coordinate of the entry), the stack label is flagged.

In other words, look up each column's coordinates and see if there is a column within +/- 0.25 of it, and if so identify which column.

What I have been trying is an array formula where the X of the entry column subtracts each X and the Z subtracts each Z, and if <.25, checks to make sure the difference >0, all within IF and AND statements... which almost worked except I need to look at X and Z as a coordinate, not separate values.

Attached is the file, any thoughts, ideas, suggestions, or formulas would be appreciated!!!

Thank you very much in advance!

Array lookup within a range.xlsm  Register To Reply

2. ## Re: Find any coordinates within +/- 0.25 of a given coordinate

Brain scrambled, try
Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: Find any coordinates within +/- 0.25 of a given coordinate

Hey jason, thanks for the reply.... I think I see where you're going with this... but how are you using the COUNTIFS? I get you're using countif to see how many x or z coordinates are within +/-.25, but how does that work with AND? Maybe COUNTIF > 1? But even then, I could get a false positive because there might be an x that is within .25, and a z within .25, but not the same coordinate pair.  Register To Reply

4. ## Re: Find any coordinates within +/- 0.25 of a given coordinate

At some point I will try to figure out why I used AND() when it was only going to break the formula. Need to unscramble brain mush first One countifs function with 6 criteria instead of 2 functions with 3 in each should work.

Formula:  `Please Login or Register  to view this content.`

This way x and z would be compared as the same pair.  Register To Reply

5. ## Re: Find any coordinates within +/- 0.25 of a given coordinate

almost. it only works if both x and z are <.25, but it should still say OFF if only 1 coordinate is off  Register To Reply

6. ## Re: Find any coordinates within +/- 0.25 of a given coordinate

Try changing AND() to OR() in the first formula.  Register To Reply