Fastest way to match longitutdes and latitudes, thousands of data points

**Oredigger90** · 08-28-2012, 11:56 AM

Hello all,

I am really new to programming in VBA and Excel, but have a lot of experience in C++ and Matlab. I have thousands (anywhere from 20,000 to 500,000) of data points to match using latitude and longitude (two columns against two columns), and then must export those matching points and their other associated values into two new columns in order to run analysis on the acceleration values. Each "row" of data contains 8 columns, so my smallest file size is 2 x 8 x 20,000 filled boxes (320,000 boxes). I have written a For/Next code that works on small files (around 200 rows each), but am just trying to find the easiest method to actually match the data without crashing this cheaper computer.

**MarvinP** · 08-28-2012, 12:03 PM

Hi Oredigger90 and welcome to the forum,

I just answered a question with Advanced Filters and think that will work here too. NO VBA needed and you can copy the resulting matches to a different part of a sheet.

See http://www.contextures.com/xladvfilter01.html

If you need more examples then attach a sample with some data and what you want to find and we can show you how to do it.

**Oredigger90** · 08-28-2012, 12:54 PM

My formula was the following, but will not run quickly so I will try your solution:

Sub Matching()

Dim rows1 As Long, rows2 As Long, i As Long, j As Long, result As Boolean

rows1 = Range("B" & Rows.Count).End(xlUp).Row

rows2 = Range("L" & Rows.Count).End(xlUp).Row


i = 1
j = 1

For i = 1 To rows1

For j = 1 To rows2



    If (Range("B" & i).Value = Range("L" & j).Value) And (Range("C" & i).Value = Range("M" & j).Value) Then
    result = True
    Range("I" & i).Value = result
    Range("S" & j).Value = result
   End If
 
Next j

Next i



End Sub

'Also, I was wondering if VLOOKUP would be a viable solution? I haven't used it and don't really know how, but I will try. I have also posted some example data, only the matching latitudes and longitudes should be copied into a separate sheet next to eachother.

**Cutter** · 08-28-2012, 02:05 PM

@ Oredigger90

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.

**Oredigger90** · 08-29-2012, 05:07 PM

I have matched the points using =iferror(match(),0) and deleting all rows of zeros, and after a lot of copy/pasting, have got them into the columns shown in my attached example. I wrote a code that successfully matches them into their particular latitudes and longitudes, but it crashes my computer lol. So if there is a simpler solution than I am seeing, please help.