continuation and modification of previous question: match values from two different spreadsheets and insert

So now, getting back to the previous question:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28546889.html

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.  Some have tons!

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? (Or it can have another separator if needed.)
mabehrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamCommented:
please see the attached example. it the UDF will do the job for you.
EE.xlsm
0
mabehrAuthor Commented:
hmmm.... thank you, but how do I use this? I have two spreadsheets. I don't see how this accesses the USZipOnly.xls to populate the K column in Maptitude_FMG_AllGoodEmails.xlsm. Am I missing something?
0
ProfessorJimJamCommented:
if you attach your files then i will embed them for you.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

mabehrAuthor Commented:
0
ProfessorJimJamCommented:
mabehr


your file Maptitude was a mess, you had numerous whole column conditional formatting in it. which makes the workbook complete dead slow. i had to remove the conditional formatting,

the formula to work is the following.  when both workbooks are open.

because of your file is too big and the Aconcat UDF is not a builtin function, it would calculate slow in huge data.
currently, as a test in the attached file, i have copied the formula up to row number 2872 .

for this to work, you need to allocate approx 30 minute of your time. i did not have 30 minutes that is why i only did it for 2872 rows.

so here you need to do.  in order for the formula not to get frozen with too much recalculation of cells.

then you do it as follows.

take each couple of hundreds of rows and paste the formula, it would take few seconds for the formula to calculate and return the values and once done, then copy same cells and paste over as values, so that when next time you do it for the next batch of cells the previous cells would not recalculate.

even right now when you open the Maptidue file it will take about 30 seconds for the file to open and recalculate cells, so then to start copy all those rows and then paste them as values before you move on to the next batch of copy and paste.

also when you paste as values, make sure that you do not take the last row that has formula, so that for the next batch you have a cell with formula that you can copy.

this is the formula on the begining of cell

=SUBSTITUTE(AConcat(IF([USZipOnly.xls]Query1!B$2:B$43630=I2,IF([USZipOnly.xls]Query1!C$2:C$43630=J2," I "&[USZipOnly.xls]Query1!A$2:A$43630,""),""))," I ","",1)
Maptitude-FMG-AllGoodEmails-scrubbed.xls
USZipOnly.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mabehrAuthor Commented:
That worked (and is still processing away). Thank you.

The next and final thing I need to do is do another finding and matching. I will post the link to the question here if you are interested.
0
mabehrAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.