excel 2010 - find matching value with two sheets then paste value....

I have two spreadsheets: MSawyer_10_28_14_Maptitude_D_scrubbed.xlsx and Maptitude_FMG_AllGoodEmails_11-4_scrubbed.xlsm.

I need a function that will do the following:

Where in Col E of MSawyer_10_28_14_Maptitude_D_scrubbed.xlsx = a value in Col K of Maptitude_FMG_AllGoodEmails_11-4_scrubbed.xlsm paste the value of Col F in MSawyer_10_28_14_Maptitude_D_scrubbed.xlsx to Col O of MMaptitude_FMG_AllGoodEmails_11-4_scrubbed.xlsm.

And since Col K of Maptitude_FMG_AllGoodEmails_11-4_scrubbed.xlsm has so many values there may be more than one name that goes in Col O of Maptitude_FMG_AllGoodEmails_11-4_scrubbed.xlsm

Files are attached.

MSawyer-10-28-14-Maptitude-D-scrubbed.xlMaptitude-FMG-AllGoodEmails-11-4-scrubbe
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.

Rob HensonFinance AnalystCommented:
My initial thought is that you can use an INDEX/MATCH to pull the required value from the source document, with the following syntax:

=INDEX(result_range,MATCH(lookup_value,lookup_range,type))

result_range = Col F in MSawyer_10_28_14_Maptitude_D_scrubbed.xlsx
lookup_value = the value from Col E of MSawyer_10_28_14_Maptitude_D_scrubbed.xlsx
lookup_range = Col K of MSawyer_10_28_14_Maptitude_D_scrubbed.xlsx
type = 0 as this will generate an exact MATCH.

This will only return one value, the first occurence of a match in col K.

To accommodate multiple occurences, a couple of questions:
Where are you wanting the results of the multiple occurences; additional columns to the right, concatenated in the cell??
Is the source document sorted in order, will the multiple occurences be sequential?

Thanks
Rob H
0
mabehrAuthor Commented:
Thanks Rob.

I think additional columns would be best and that can continue from Col O, so Col P, Q, R, S - etc.

It looks like they are all sequential.
0
Rob HensonFinance AnalystCommented:
OK, I will take a look. To explain the syntax of the above, the formula is using relative positioning to return the value. The MATCH will find the row number of the lookup_value and the INDEX returns the value from the same row in the result_range.

I am thinking that subsequent results can be found using a count of results and then incrementing the MATCH value by one until the count is reached.

Thanks
Rob H
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Rob HensonFinance AnalystCommented:
Looking at the files that you uploaded and I don't think it is going to be as simple as I thought with INDEX / MATCH.

Also, the files don't appear to agree with your description of the scenario.

Where in Col E of MSawyer_10_28_14_Maptitude_D_scrubbed.xlsx = a value in Col K of Maptitude_FMG_AllGoodEmails_11-4_scrubbed.xlsm paste the value of Col F in MSawyer_10_28_14_Maptitude_D_scrubbed.xlsx to Col O of MMaptitude_FMG_AllGoodEmails_11-4_scrubbed.xlsm.

Col E of MSawyer_10_28_14_Maptitude_D_scrubbed.xlsx = Single Value (ZIP Code?)
Col K of Maptitude_FMG_AllGoodEmails_11-4_scrubbed.xlsm = Multiple values per cell, separated with pipe symbol
Col F in MSawyer_10_28_14_Maptitude_D_scrubbed.xlsx = Blank

Please explain.

Thanks
Rob H
0
Rob HensonFinance AnalystCommented:
Am I right in interpreting requirement as:

Formula in column O (and beyond) which does:

Look at each value in col K of AllGoodEmails, find that value in col E of MSawyer file and return value from col F of MSawyer file.

If interpreted correctly, as mentioned earlier a lot (75%) of col F is blank.

Thanks
Rob H
0
mabehrAuthor Commented:
that is correct
0
Rob HensonFinance AnalystCommented:
Ok, try this:

In O1 of  AllGoodEmails put 1 and then P1 put =O1+8 and copy across as far as you think you need to go on row 1.

Then in O2 put this formula:

=IFERROR(IF(LEN($K2)<O$1,"",INDEX('[MSawyer-10-28-14-Maptitude-D-scrubbed.xls]ZIP Data'!$F:$F,MATCH(MID($K2,O$1,5),'[MSawyer-10-28-14-Maptitude-D-scrubbed.xls]ZIP Data'!$E:$E))),"")

Copy across as far as required and copy down.

The formula assumes that the values in K are equally spaced - "ZIP plus space plus pipe plus space" thus every 9 character in the cell is the start of a new ZIP. The row of numbers in row 1 is just giving the position of the 9th character. The MID section of the formula is pulling the string of 5 characters starting at each of the numbers in row 1. The MATCH is then finding that string in col E and returning the value from col F of other file.

My machine is running out of resources when recalculating so can't confirm that it is working correctly.

Thanks
Rob H
0
mabehrAuthor Commented:
inserted your code above (then pressed ctrl, shift, enter) and then when I attempted to copy to the right or down, a window pops up: "Update Values: MSawyer-10-28-14-Maptitude-D-scrubbed.xls"

What should I do with this and why is that happening?
0
Rob HensonFinance AnalystCommented:
You don't need to enter with Shift & Ctrl, just enter.

The uploaded files had only partial or missing extensions so saved locally with xls, maybe your proper files are xlsx or xlsm

Thanks
Rob
0
mabehrAuthor Commented:
Tried this out. Not sure it's working. All I'm getting is zero's in Col O. I've tried 1700 rows and no names are returned. You can see the attached. Maybe I'm doing something wrong.

And as a note, the other files is named: MSawyer_10_28_14_Maptitude_D_scrubbed.xls , so I adjusted your code but still it did not return any names. I only pulled down one column as anymore just took forever.

Maptitude-FMG-AllGoodEmails-11-5-scrubbe
0
Rob HensonFinance AnalystCommented:
I was also only getting 0 but assumed it was because the result in col F was blank. I did not try with one where we knew there was a value in col F.
0
mabehrAuthor Commented:
In the MSawyer spreadsheet there is a city, STATESBORO (GA), with a zip code of 30458 and has a name of Bunter Bice in Col F.

I did a search for STATESBORO in the Maptitude spreadsheet and found both Stateboro for the city and the 30458 as a zip code however the function didn't return the name it should have which was Bunter Bice. It returned a blank cell.

You can see the two spreadsheets I used, attached.
MSawyer-10-28-14-Maptitude-D-scrubbed.xl
Maptitude-FMG-AllGoodEmails-11-5-scrubbe
0
Rob HensonFinance AnalystCommented:
Found the result and it is such a simple fix, almost schoolboy error!!!

=IFERROR(IF(LEN($K2)<O$1,"",INDEX('[MSawyer-10-28-14-Maptitude-D-scrubbed.xls]ZIP Data'!$F:$F,MATCH(MID($K2,O$1,5),'[MSawyer-10-28-14-Maptitude-D-scrubbed.xls]ZIP Data'!$E:$E,0))),"")

The only change is in bold and underlined.

Omitting the ",0" from the MATCH statement was telling it to find a close match in a sorted list and stop looking if list value goes greater than the lookup_value. Including the ",0" tells it to find an exact match and ignore sort order of list.

Thanks
Rob H
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:
This appears to be working now, but it is soooo slooooowwwww...... Anyway, I'll know for sure later. Thank you.
0
Rob HensonFinance AnalystCommented:
There is a lot of data to calculate. If I recall there was nearly 20k rows so just copying across 10 columns gives 200k calculations.
0
mabehrAuthor Commented:
true, but I'm only copying one column to start. but it's over 200K rows.
0
mabehrAuthor Commented:
Thank you Rob. Worked as advertised. Much appreciated.
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.