Solved

match values from two different spreadsheets and insert

Posted on 2014-10-29
16
88 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 25

Expert Comment

by:ProfessorJimJam
Comment Utility
=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
Comment Utility
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 25

Expert Comment

by:ProfessorJimJam
Comment Utility
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
 

Author Comment

by:mabehr
Comment Utility
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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 25

Expert Comment

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

Author Comment

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

Author Comment

by:mabehr
Comment Utility
folder
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 25

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
hmmm... I'm selecting US_2.xls but it uploads and posts on ee as US-2.xls.

Strange.
US-2.xls
0
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
0
 

Author Comment

by:mabehr
Comment Utility
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
Comment Utility
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
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…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now