If two geographical data match in two sheets, then add/copy/replicate unique id/code found in one sheet

[alliage]

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?

 

[Marcelo Branco]

Hi,

Not sure this is the best (or faster) way, but if the Geographical Code is a Number it can work

Formula in Sheet2 A2 copied down

=SUMPRODUCT(Sheet1!$A$2:$A$40000,--(Sheet1!$B$2:$B$40000=A2),--(Sheet1!$C$2:$C$40000=B2),--(Sheet1!$D$2:$D$40000=C2),--(Sheet1!$E$2:$E$40000=D2))

HTH

M.

 

[alliage]

It's hard to visualize your suggestion, my friend. If it's okay for you, I would like to e-mail you my data, so that you cann look at it and analyze.

Here's my email: alliage.morales[@]gmail.com
Give me an e-mail, so I could e-mail you this.

I'm working at a local news broadcast network. We're working on a story about our own version of the conditional cash transfer, your country's Bosa Familia program.

 

[Marcelo Branco]

I'm working at a local news broadcast network. We're working on a story about our own version of the conditional cash transfer, your country's Bosa Familia program.

Interesting!

M.
ps: i've sent a PM to you

 

[alliage]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> To Mr. Branco,

First of all, your suggestion really worked. It did help me a lot.

Thank you very much for your assistance in helping me with problem.

Now, I can finally study our government’s program with impartiality.

I hope we could get in touch for future queries/problems on Excel.

Again, I can’t thank you enough for helping me. J

Alliage Morales.

 

[Marcelo Branco]

To Mr. Branco,

First of all, your suggestion really worked. It did help me a lot.

Thank you very much for your assistance in helping me with problem.

Now, I can finally study our government’s program with impartiality.

I hope we could get in touch for future queries/problems on Excel.

Again, I can’t thank you enough for helping me. J

Alliage Morales.

Mr Morales,

Thank you very much for your kind words.

It was a pleasure to help and i am sure that you, and your research team, will do a great service studying seriously, a so important but controversial, government's program of my country.


All the best

M.

 

[alliage]

Mr. Branco, I did email you something. I have one query re: the formula.