+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    10-02-2015
    Location
    Los Angeles
    MS-Off Ver
    Excel 2013
    Posts
    6

    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
    Attached Files Attached Files
    Last edited by nakfseng; 10-02-2015 at 12:55 PM.

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

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

    Brain scrambled, try
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-02-2015
    Location
    Los Angeles
    MS-Off Ver
    Excel 2013
    Posts
    6

    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.

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    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: copy to clipboard
    Please Login or Register  to view this content.

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

  5. #5
    Registered User
    Join Date
    10-02-2015
    Location
    Los Angeles
    MS-Off Ver
    Excel 2013
    Posts
    6

    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

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Need a formula to find the value listed at an X Y coordinate
    By BeachRock in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-20-2014, 02:04 PM
  2. Replies: 7
    Last Post: 07-29-2013, 04:51 PM
  3. Replies: 0
    Last Post: 10-16-2012, 04:27 AM
  4. [SOLVED] VBA to find cell with a specific value, and ID its coordinates
    By artkingjw in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-29-2012, 01:17 AM
  5. Find (x,y) coordinate, given target Slope
    By scope951 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-03-2010, 03:43 PM
  6. return value of X coordinate and Y coordinate?
    By sbmoller in forum Excel General
    Replies: 1
    Last Post: 09-22-2007, 06:54 AM
  7. [SOLVED] Find specific text and return coordinates
    By BOONER in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-09-2006, 04:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1