Matching Values

smurray444

New Member
Joined
Nov 21, 2005
Messages
48
Dear all,

I have around 30 files, and each of these contain millions of rows (I'm
using Excel 2007), and three columns. Column A = longitude, column B =
latitude, column C = value.

I have a reference file of the same format as described above, which has all the 'master' longitude and latitude values. The other files have less rows, since they do not contain all the longitude and latitude values that are present in the reference file. What I want to be able to do is for the files with less rows, compare each row to the reference file, and if a latitude and longitude coordinate for a particular row is not present, then insert this in place, and give the value (column C) as zero. Where present, values in columns 1 and 2 should be in sequence (i.e. follow the same pattern as in the reference file), so where the sequence is 'broken', a latitude and longitude can be assumed missing, and a new row inserted with the 'missing' longitudes and latitudes, with zero in column 3.

Below is a link to a couple of sample files. The master/reference file is
'o_less2_year3', with an example of a data file needing to be padded out with zeros and the associated 'missing' coordinate row (in relation to the master file) being 'out_lpj_year1990'. These fit onto 2 worksheets in Excel 2007, yet if you're not using Excel 2007, then I'm sure working on a single sheet's worth of data would still be more than enough to gain a solution!

http://www.megaupload.com/?d=INNY8IP3

I hope I've made myself clear enough - if not, please do not hesitate to get in touch with me.

Many thanks for your help,
smurray444
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
If I were to copy your reference file and fill col C to Ag with values from 30 files against corresponding co-ordinates and fill the rest with zero, will it still serve your needs?
RAvi
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Not sure if this is what you wanted
Code:
Sub test()
Dim a, i As Long, b(), n As Long
Dim myDir As String, fn As String, z As String
myDir = "c:\test\"  '<- change here to suite
fn = Dir(myDir & "*.xls")
a = ThisWorkbook.Sheets(1).Range("a1").Resize(,3).Value
ReDim b(1 To Rows.Count - UBound(a,1), 1 To 3)
With CreateObject("Scripting.Dictionary")
     For i = 1 To UBound(a,1)
          z = a(i,1) & ";" & a(i,2)
          If Not .exists(z) Then .add z, Nothing
     Next
     Do While fn <> ""
          With Workbooks.Open(myDir & fn)
               a = .Sheets(1).Range("a1").CurrentRegion.Resize(,3).Value
               For i = 1 To UBound(a,1)
                   z = a(i,1) & ";" & a(i,2)
                    If Not .exists(z) Then
                         n = n + 1
                         b(n,1) = a(i,1) : b(n,2) = a(i,2) : b(n,3) = 0
                         .add z, Nothing
                    End If
               Next
               .Close False
          End With
          fn = Dir()
     Loop
End With
ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(n,3).Value = b
End Sub
 

smurray444

New Member
Joined
Nov 21, 2005
Messages
48
Thanks for your effort - however, I get an error at this line: ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(n, 3).Value = b ...which is runtime error 1004, 'application defined or object defined error'. Any ideas?

In any case, I'm wondering whether the code above is suitable, as it seems to only refer to one one file? Ideally I'd need something that compares one file to the master file.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
try this one.
Code:
Sub test()
Dim a, i As Long, b(), n As Long
Dim myDir As String, fn As String, z As String
myDir = "c:\test\"  '<- change here to suite
fn = Dir(myDir & "*.xls")
a = ThisWorkbook.Sheets(1).Range("a1").Resize(,3).Value
ReDim b(1 To Rows.Count - UBound(a,1), 1 To 3)
With CreateObject("Scripting.Dictionary")
     For i = 1 To UBound(a,1)
          z = a(i,1) & ";" & a(i,2)
          If Not .exists(z) Then .add z, Nothing
     Next
     Do While fn <> ""
          With Workbooks.Open(myDir & fn)
               a = .Sheets(1).Range("a1").CurrentRegion.Resize(,3).Value
               For i = 1 To UBound(a,1)
                   z = a(i,1) & ";" & a(i,2)
                    If Not .exists(z) Then
                         n = n + 1
                         b(n,1) = a(i,1) : b(n,2) = a(i,2) : b(n,3) = 0
                         .add z, Nothing
                    End If
               Next
               .Close False
          End With
          fn = Dir()
     Loop
End With
If n > 0 Then
     ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(n,3).Value = b
Else
     MsgBox "No new item found"
End If
End Sub
If you get MsgBox, then the ranges are wrong.

Can you show me the sheet layout?
 

smurray444

New Member
Joined
Nov 21, 2005
Messages
48
Hi - yeah I'm getting the message box come up. I can't seem to figure why this is. When you refer to ranges, do you mean where the data is located in the worksheet? If so, it's in column A to C (the master file), whilst the data I'm comparing this with is what I'm changing the filepath to in the code (this also has 3 columns).

HTH.
 

Forum statistics

Threads
1,181,100
Messages
5,928,058
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top