Another Excel question

tcofran

tcofran

Expert Member
Joined
Jun 17, 2009
Messages
3,305
Reaction score
855
Location
Pretoria
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
 
tcofran said:
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
Click to expand...
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.
 
Cray said:
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.
Click to expand...
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
Cray said:
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.
Click to expand...
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

Filter for unique values or remove duplicate values - Microsoft Support

How to filter for unique values in Excel, remove duplicate values, and more.
support.microsoft.com support.microsoft.com

And vlookup is ancient.

use xlookup
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter