+ Reply to Thread
Results 1 to 1 of 1

Cross reference XY coords of two data sets, find nearest points, and merge associated data

  1. #1
    Registered User
    Join Date
    08-06-2013
    Location
    the boonies
    MS-Off Ver
    Excel 2007
    Posts
    46

    Cross reference XY coords of two data sets, find nearest points, and merge associated data

    Hi, I need help either with VBA (I am a complete novice) or excel functions (likely MATCH/INDEX) in order to return the row/element number of an array. The tricky part is that the datasets I'm matching are spatially related, but due to rounding error, I cannot get a perfect match in all cases with VLOOKUP. I tried to cross reference (X*Y) with various rounding schemes including VBA Banker's Rounding, but there are always some points that do not match. Not sure if there's a way to do a double VLOOKUP since there may not be a perfect match in either X or Y -- I need to match the closest point spatially. So, I used an array to find the minimum distance between the (X,Y) of each point on sheet "A" and the data to cross reference on sheet "markers". However I cannot figure out how to get the row number of the array's underlying (X,Y,property) data (whereas the "minimum distance" is being calculated on the fly). If I had that, I could use MATCH and INDEX to grab other information from the same cross referenced data points. Seems like this might be a job for VBA, but there might be a clever way with standard Excel functions. I want the answer to only use one column, not expand to other trash/intermediate columns because that would likely cause other problems for me in an already very large and complicated spreadsheet. The real data is thousands of spatial data points times tens of different kinds of data associated with each; each one is just X,Y,property. Attached is an example that I'd appreciate your help with! Try to fit your match-up code in column "N" of sheet "markers" and/or column "E" of sheet "A". Thanks.
    Attached Files Attached Files

+ 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. data problems, matching large data sets with smaller known points
    By awguest1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-28-2013, 08:48 PM
  2. [SOLVED] Cross reference with multiple instances in reference data
    By Nick F in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-08-2013, 10:31 AM
  3. [SOLVED] Merge two data sets A & B into one
    By plumberef in forum Excel General
    Replies: 4
    Last Post: 10-25-2012, 11:04 PM
  4. [SOLVED] How to merge two data sets to create a single large data set.
    By Econocrat in forum Excel General
    Replies: 5
    Last Post: 10-06-2012, 04:02 PM
  5. [SOLVED] merge two data sets one unique
    By CESTOTT in forum Excel General
    Replies: 2
    Last Post: 01-25-2006, 11:50 AM

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