Solved

match values from two different spreadsheets and insert

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

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:mabehr
ID: 40411217
0
 
LVL 32

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 25

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 25

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 25

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

810 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