Solved

match values from two different spreadsheets and insert

Posted on 2014-10-29
16
95 Views
Last Modified: 2014-10-30
I have a spreadsheet, (US_2.xls) that has:
Col A: zip code; Col B: City; Col C: State (in CA, MO, MT format).

I have another spreadsheet (Maptitude_FMG_AllGoodEmails_2.xlsm) that has over 200,000 records:
Col i: City; Col J: State (in CA, MO, MT format); and Col K zip code (currently with no values).

They are both in the same folder.

I need a function to do the following:

In US_2.xls where Col B and Col C = Col i and Col J in Maptitude_FMG_AllGoodEmails_2.xlsm, insert the zip code from US_2.xls into Col K of Maptitude_FMG_AllGoodEmails_2.xlsm.

And, where there is more than one zip code for a city format Col K as: 94598|94599|94597 (or some other separator) and so on. Some cities will have many.
0
Comment
Question by:mabehr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
16 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40411114
=index(A:AofUS_2.xls,match(I:IofMaptitude_FMG_AllGoodEmails_2.xlsm&ofMaptitude_FMG_AllGoodEmails_2.xlsmJ:J,B:BofUS_2.xls&C:CofUS_2.xls,0))

press with control shift Enter
0
 

Author Comment

by:mabehr
ID: 40411126
Thanks Professor.

I input this formula into row 2 of the Maptitude spreadsheet and it gave me as a result: #NAME?

pressing control, shift, enter while in this cell didn't seem to do anything.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40411204
the ofMaptitude_FMG_AllGoodEmails_2.xlsm and ofUS_2.xls  that i used, is not the string to be exactly put in the formula. i used them as a metaphor to indicate reference of the workbook.  

if you could post a dummy file, then i would put the formula in the cell for you.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mabehr
ID: 40411217
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40411221
Mabehr - I don't believe the formula above from Prof JimJam was meant to be copied literally as he has typed it. For example, where he has "A:AofUS_2.xls" he is expecting you to convert that to the relevant sheet so would end up as "[US_2.xls]Sheet1!A:A" or similar.

Pressing Control, Shift & Enter is to confirm the entry into the cell, instead of just pressing enter. If you ahve already pressed enter, press F2 to go into Edit mode and then press Control, Shift & Enter.

Thanks
Rob H
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40411250
check the attached file.  i created some dummy zipcodes and see the result
Maptitude-FMG-AllGoodEmails-dummy.xlsm
0
 

Author Comment

by:mabehr
ID: 40411286
not sure what I'm doing wrong. please see attached
zip-code-error.jpg
0
 

Author Comment

by:mabehr
ID: 40411292
folder
0
 
LVL 26

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 500 total points
ID: 40411313
see this formula insert while both of the below files are open  =INDEX(US_2.xls!$A:$A,MATCH('FMG All Good Emails_July_Apport'!I2&'FMG All Good Emails_July_Apport'!J2,US_2.xls!$B:$B&US_2.xls!$C:$C,0))

CTR SHIFT ENTER KEYS

both files attached.
Maptitude-FMG-AllGoodEmails-dummy.xlsm
US-2.xls
0
 

Author Comment

by:mabehr
ID: 40411380
still not getting results. Inserting the formula you state above with both open (US_2.xls and Maptitude_FMG_AllGoodEmails_2.xlsm) but get a #N/A response. See attached and also uploaded the US_2.xls if that helps.

results
US-2.xls
0
 

Author Comment

by:mabehr
ID: 40411397
hmmm... I'm selecting US_2.xls but it uploads and posts on ee as US-2.xls.

Strange.
US-2.xls
0
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 40411428
you get N/A error becuase check your city name and state in the I and J they are completely different and cannot be found in the US_2.xls file.  now i have put some data from the US_2.xls file into the main file and it works.  check the attached files.

both files must be saved in same location and both of them need to be open to work.
Maptitude-FMG-AllGoodEmails-dummy.xlsm
US-2.xls
0
 

Author Closing Comment

by:mabehr
ID: 40411489
Thank you. I think this works. The problem is, as you said, it can't find the data from US_2.xls which is a problem I'm going to post a question for next. I'll post a copy of it here if you care to look at it. Thank you.
0
 

Author Comment

by:mabehr
ID: 40411556
0
 

Author Comment

by:mabehr
ID: 40413478
So now, getting back to this question.

I now have USZipOnly.xls, (instead of US_2.xls) and when I replace
US_2.xls with USZipOnly.xls in the above function I do get results but I should be getting multiple results for almost all cities
as almost all cities have more than one zip code.

Can this solution/function be updated so what it will return multiple zip codes per city in this format?

94597|94598|94599 and so on?

I will ask this on another question and post a link to it here.
0
 

Author Comment

by:mabehr
ID: 40413501
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question