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
Solved

match values from two different spreadsheets and insert

Posted on 2014-10-29
16
93 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 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

792 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