Link to home
Start Free TrialLog in
Avatar of mabehr
mabehr

asked on

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
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of mabehr
mabehr

ASKER

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.
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
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
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
Avatar of mabehr

ASKER

that is correct
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
Avatar of mabehr

ASKER

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?
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
Avatar of mabehr

ASKER

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
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.
Avatar of mabehr

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mabehr

ASKER

This appears to be working now, but it is soooo slooooowwwww...... Anyway, I'll know for sure later. Thank you.
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.
Avatar of mabehr

ASKER

true, but I'm only copying one column to start. but it's over 200K rows.
Avatar of mabehr

ASKER

Thank you Rob. Worked as advertised. Much appreciated.