Merging worksheets in Excel.

March 3, 2009 1:15 PM

Excel-Fu Filter: I need to merge latitude and longitude values (in two columns) corresponding to a specific site from one worksheet to the corresponding site in another worksheet.

For example, in the Lat/Long worksheet column A is the site (AK03), B is Latitude, C is the Longitude. The other worksheet has column A (Site) and B (pH). The problem with a simple copy/paste is that Worksheet 2 has multiple listings for each site.

There must be a simple formula that would accomplish this, but I have searched Excel Help and many forums and am no closer to a solution. Hope me.

posted by schyler523 to computers & internet (7 comments total)

Have you tried vlookup? It should do what you want, if I understand your question. I'll be glad to expand if you have never used it.
posted by TurnedIntoANewt at 1:28 PM on March 3

Put this in column C of Sheet2:
=vlookup(A1,[Sheet1]!A$1:C$1000,2,false)

Put this in column D of Sheet2:
=vlookup(A1,[Sheet1]!A$1:C$1000,3,false)

You can then select the cells in columns C and D for each row, and Ctrl-D to "Fill Down". This formula includes 1000 rows of the first sheet, change this to the length of your data. Note the dollar signs in the table array -- Excel needs these to maintain the same values when doing the fill. Also, the values in Sheet1!A:A (column A) must be in order or vlookup won't find them correctly.
posted by greensweater at 2:07 PM on March 3

Is the data in worksheet 2 sorted by site?
What are the maximum datapoints / site that you have? (3 ph's, 5, 20?)
I would in worksheet A, start in column D. Do a

=match(A1, 'worksheet2!A:A',0) This will find where the data for that site starts.
In the next column (E), you can then find your first pH with =index(worksheet2!B:B,D1)
in the next column (F), you can find your second datapoint with =index(worksheet!B:B, D1+1)

If you have different amounts of pH readings (say 20 for site one, but only 3 for site 2) you can use an if statement to check and make sure that it's still the same site, otherwise display a blank.

=if(index(worksheet!A:A,D1+1)=A1, index(worksheet2!B:B,D1+1), "")


I'm sure there is a more clever way of doing it, and I know my sintax isn't perfect, but maybe this gives you an hint.
posted by defcom1 at 2:08 PM on March 3

greensweater is the person to listen to, if my understanding of your problem is correct.

Sort your list of sitecode/lat/lon to be in ascending sitecode order, then use vlookup from your second worksheet to look up the lat and lon for a given sitecode.
posted by Mike1024 at 2:44 PM on March 3

vlookup does both the match and index functions at the same time. It looks like a complicated function, but the function wizard makes it a lot easier. Just make sure the last argument in the function (range_lookup) is false, otherwise the function will try to pick a close match. Better to let it return an error so you can see it.
posted by TurnedIntoANewt at 2:51 PM on March 3

Although I found a workaround (I turned the databases into CSVs and added them to ArcGIS, then joined them at the SiteID,) for posterity it looks as if greensweater and TurnedIntoANewt had it right. Thanks all.
posted by schyler523 at 5:07 PM on March 3

Oh, once I had the tables joined in ArcGIS, I exported the data back into Excel. Fun times.
posted by schyler523 at 5:09 PM on March 3