Solved

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

Posted on 2014-11-04
17
81 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
  • 9
  • 8
17 Comments
 
LVL 31

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 31

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
 
LVL 31

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 31

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 31

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 31

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 31

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 31

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 31

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now