Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

match values from two different spreadsheets and insert

Posted on 2014-10-29
16
Medium Priority
?
99 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
  • 10
  • 5
16 Comments
 
LVL 27

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 27

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
Technology Partners: 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!

 
LVL 34

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 27

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 27

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 2000 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 27

Accepted Solution

by:
ProfessorJimJam earned 2000 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: 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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

885 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