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