Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-31-2011, 11:35 PM
alliage alliage is offline If two geographical data match in two sheets, copy unique id/code found in one sheet Windows XP If two geographical data match in two sheets, copy unique id/code found in one sheet Office 2003
Novice
If two geographical data match in two sheets, copy unique id/code found in one sheet
 
Join Date: Aug 2011
Posts: 1
alliage is on a distinguished road
Default If two geographical data match in two sheets, copy unique id/code found in one sheet

Hi guys, this is my problem.

I have a geographical data (divided into four colums per REGION, PROVINCE, MUNICIPALITY/TOWN, and BARANGAY/STREET) in one sheet. This sheet contains a UNIQUE GEOGRAPHICAL CODE that IS NUMERICAL IN NATURE.

Now, I have also another sheet which lists the same geographical data (divided into four colums per REGION, PROVINCE, MUNICIPALITY/TOWN, and BARANGAY/STREET). The geographic areas included in this sheet are recipients of one government program. But this sheet does not contain the UNIQUE GEOGRAPHICAL CODE.

What I would like to do is to match the geographical data found in two sheets, and if they match, copy/replicate/add the UNIQUE GEOGRAPHICAL CODE found in sheet to the other sheet that does not contain the code.

I would like to include the geographical code, so that it would become the primary key when I create a database in MS Access.



This is the example

SHEET 1
A B C D E
0019 REGION I BASILAN PROVINCE TIPO MUNICIPALITY BORAS STREET

*Column A contains the UNIQUE GEOGRAPHICAL CODE

SHEET 2
A B C D
REGION I BASILAN PROVINCE TIPO MUNICIPALITY BORAS STREET

*Sheet 2 contains only 20k entries of geographical areas while Sheet 1 contains about 40k entries. Only half of the geographical areas are included in the government program

Can you help me?
Attached Files
File Type: zip 2011_OFFICIAL_GEOGRAPHIC_CODE.zip (1.96 MB, 19 views)
Reply With Quote
  #2  
Old 09-01-2011, 05:23 AM
OTPM OTPM is offline If two geographical data match in two sheets, copy unique id/code found in one sheet Windows 7 32bit If two geographical data match in two sheets, copy unique id/code found in one sheet Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi
Here is the formula to use:

=VLOOKUP(F2,[2011_OFFICIAL_GEOGRAPHIC_CODE.xls]GEOGCODE!$E:$F,2,FALSE)

However in the worksheet you are reading the code from the code column will need to be in Column F. The formula will look for the STREET in Column E and return the code from Column F.
This formula should be in column A in your target worksheet otherwise you will need to change the references slightly.
Hope this helps.
Any problems come back to the forum.
Good luck.
Tony(OTPM)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
If two geographical data match in two sheets, copy unique id/code found in one sheet How to syncronize data in two different sheets diegogeid Excel 2 09-30-2010 12:19 AM
If two geographical data match in two sheets, copy unique id/code found in one sheet How do I merge data from one sheet in a workbook out into multiple sheets nolesca Excel 4 06-07-2010 08:13 AM
If two geographical data match in two sheets, copy unique id/code found in one sheet Any tutorial same as to this excel sheet with code? Learner7 Excel 1 05-18-2010 05:31 PM
copy cell from sheet 2 to sheet 3 macro slipperyjim Excel Programming 1 02-18-2010 01:31 AM
How can I use a colom of data if a specific value is found. Grapejuice Excel 1 08-11-2006 11:44 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:14 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft