Another Excel question

tcofran

Expert Member
Joined
Jun 17, 2009
Messages
3,246
Good morning MyBB

I have 2 spreadsheets.....

File 1 = list with about 40000 rows, with coordinates
File 2 = file 1 was filtered by someone, to bring it down to about 400 rows, but the coordinates column was removed, along with alot of information that is not needed. I need the coordinates to plot the info on a GIS system

both files have a column that contains a a unique code that i can use.

how can i merge the 2 files, to get the coords on file 2 ??

thanks
 
Probably more towards data consolidation?
I am no excel person but what he described seemed like something I did couple of months ago. I remember I used Lookup function based on unique code and then populated missing column values.
 
Good morning MyBB

I have 2 spreadsheets.....

File 1 = list with about 40000 rows, with coordinates
File 2 = file 1 was filtered by someone, to bring it down to about 400 rows, but the coordinates column was removed, along with alot of information that is not needed. I need the coordinates to plot the info on a GIS system

both files have a column that contains a a unique code that i can use.

how can i merge the 2 files, to get the coords on file 2 ??

thanks
Have you tried Vlookup?

=VLOOKUP(A2,Sheet2!A:B,2,0)

This assumes you have both data sets in one file with two worksheets.

So in the first worksheet, column two is the column you want to populate. Lookup the value in column A2, then match to the values in sheet2 Columns A & B. Will return matching coordinates and populate in A2.

If that works, just drag the formula down using the little plus at the bottom right of the cell with the formula.

Obviously depends on how many columns of data you have but the idea is to search on your unique key in sheet1, match to unique in sheet 2 and then return the matching co-ordinates in sheet 2 to sheet 1.
 
But isn't that what data consolidation does. It compares two files and merge the info that is missing.
 
Have you tried Vlookup?

=VLOOKUP(A2,Sheet2!A:B,2,0)

This assumes you have both data sets in one file with two worksheets.

So in the first worksheet, column two is the column you want to populate. Lookup the value in column A2, then match to the values in sheet2 Columns A & B. Will return matching coordinates and populate in A2.

If that works, just drag the formula down using the little plus at the bottom right of the cell with the formula.

Obviously depends on how many columns of data you have but the idea is to search on your unique key in sheet1, match to unique in sheet 2 and then return the matching co-ordinates in sheet 2 to sheet 1.
thanks will give this a go

wish me luck
 
Use the unique code in File 2 to look up the same unique code in File 1. When the code is found, get Excel to take the coordinate from File 1 and copy it to File 2.

The formula to use is @Vlookup.
 
  • Like
Reactions: rh1
Have you tried Vlookup?

=VLOOKUP(A2,Sheet2!A:B,2,0)

This assumes you have both data sets in one file with two worksheets.

So in the first worksheet, column two is the column you want to populate. Lookup the value in column A2, then match to the values in sheet2 Columns A & B. Will return matching coordinates and populate in A2.

If that works, just drag the formula down using the little plus at the bottom right of the cell with the formula.

Obviously depends on how many columns of data you have but the idea is to search on your unique key in sheet1, match to unique in sheet 2 and then return the matching co-ordinates in sheet 2 to sheet 1.
The sheet with the values you are looking up, that column A needs to have unique values otherwise vlookup will return the first match or nearest match depending on your parameters.

To see if your column is unique try

And vlookup is ancient.

use xlookup
 
Back
Top