Solved

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

Posted on 2014-11-04
17
92 Views
Last Modified: 2014-11-13
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
0
Comment
Question by:mabehr
[X]
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
  • 9
  • 8
17 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40422153
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
 

Author Comment

by:mabehr
ID: 40422173
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40423743
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 40423780
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40423788
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
 

Author Comment

by:mabehr
ID: 40423793
that is correct
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40423808
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
 

Author Comment

by:mabehr
ID: 40424195
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40424243
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
 

Author Comment

by:mabehr
ID: 40424837
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40425159
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
 

Author Comment

by:mabehr
ID: 40425263
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
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40425809
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
 

Author Comment

by:mabehr
ID: 40427141
This appears to be working now, but it is soooo slooooowwwww...... Anyway, I'll know for sure later. Thank you.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40427352
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
 

Author Comment

by:mabehr
ID: 40427354
true, but I'm only copying one column to start. but it's over 200K rows.
0
 

Author Closing Comment

by:mabehr
ID: 40440387
Thank you Rob. Worked as advertised. Much appreciated.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

691 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