Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating a column in a worksheet from another worksheet based on a common field
Hey folks...
I know this has had to have been done before. Basically, I have an original worksheet, and another worksheet that contains values that I want to populate a column in the original worksheet with, based on a common field. But it gets tricky, because the 2nd worksheet contains some IDs (common field values) that are NOT in worksheet 1. And I'd like to know which ones those are, preferably with them being put into a 3rd worksheet automatically... here is the original spreadsheet in a nutshell: PARCEL_ID | ELEVATION 1111 2222 3333 4444 The elevation field is empty--- it's the column I want to populate using the output from a model I ran with the same parcel data, except the model's parcel data includes some additional parcels which are not part of the original spreadsheet. Here's an example-- PARCEL_ID | ELEVATION 1111.......................20 2222.......................21 7777.......................22 8888.......................23 (ignore the periods...they're place holders) So, as you can see, I have a common field. I want to simply populate spreadsheet 1 with the elevations from spreadsheet 2...but spreadsheet 2 contains some parcels that aren't in spreadsheet 1, and I'd like them to be placed in a new speadsheet by themselves. Any ideas? Thanks in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating a column in a worksheet from another worksheet based on a common field
Hi
Use VLOOKUP on your original data to pull the data from the second sheet. You will get something like this. The N\A indicates the parcel ID is not on the second sheet. PARCEL_ID | ELEVATION 1111.............20 2222.............21 3333.............N\A 4444.............N\A Now do VLOOKUP on the second sheet to try and put the data back in a third column. You will get something like this. PARCEL_ID | ELEVATION | 3rd Column 1111.......................20............20 2222.......................21............21 7777.......................22............N\A 8888.......................23...........N\A The N/A's are on your second sheet but not on your original. Now filter the second sheet on the third column, looking for N\A's. Paste this filtered data onto a new sheet and you will have what you want. regards Paul wrote: Hey folks... I know this has had to have been done before. Basically, I have an original worksheet, and another worksheet that contains values that I want to populate a column in the original worksheet with, based on a common field. But it gets tricky, because the 2nd worksheet contains some IDs (common field values) that are NOT in worksheet 1. And I'd like to know which ones those are, preferably with them being put into a 3rd worksheet automatically... here is the original spreadsheet in a nutshell: PARCEL_ID | ELEVATION 1111 2222 3333 4444 The elevation field is empty--- it's the column I want to populate using the output from a model I ran with the same parcel data, except the model's parcel data includes some additional parcels which are not part of the original spreadsheet. Here's an example-- PARCEL_ID | ELEVATION 1111.......................20 2222.......................21 7777.......................22 8888.......................23 (ignore the periods...they're place holders) So, as you can see, I have a common field. I want to simply populate spreadsheet 1 with the elevations from spreadsheet 2...but spreadsheet 2 contains some parcels that aren't in spreadsheet 1, and I'd like them to be placed in a new speadsheet by themselves. Any ideas? Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add data based on a common field | Excel Worksheet Functions | |||
copy graph from a Worksheet based on a field in another Worksheet | Excel Worksheet Functions | |||
filtering out records from one worksheet by comparing a field in it with a field in another worksheet | New Users to Excel | |||
Populating a field based on lookup values | Excel Worksheet Functions | |||
Fill a column in a worksheet from a formula worksheet based on the date | Excel Programming |