S
smurray444
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,
Steve
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,
Steve