mabehr
asked on
excel 2010 - find matching value with two sheets then paste value....
I have two spreadsheets: MSawyer_10_28_14_Maptitude _D_scrubbe d.xlsx and Maptitude_FMG_AllGoodEmail s_11-4_scr ubbed.xlsm .
I need a function that will do the following:
Where in Col E of MSawyer_10_28_14_Maptitude _D_scrubbe d.xlsx = a value in Col K of Maptitude_FMG_AllGoodEmail s_11-4_scr ubbed.xlsm paste the value of Col F in MSawyer_10_28_14_Maptitude _D_scrubbe d.xlsx to Col O of MMaptitude_FMG_AllGoodEmai ls_11-4_sc rubbed.xls m.
And since Col K of Maptitude_FMG_AllGoodEmail s_11-4_scr ubbed.xlsm has so many values there may be more than one name that goes in Col O of Maptitude_FMG_AllGoodEmail s_11-4_scr ubbed.xlsm
Files are attached.
MSawyer-10-28-14-Maptitude-D-scrubbed.xlMaptitude-FMG-AllGoodEmails-11-4-scrubbe
I need a function that will do the following:
Where in Col E of MSawyer_10_28_14_Maptitude
And since Col K of Maptitude_FMG_AllGoodEmail
Files are attached.
MSawyer-10-28-14-Maptitude-D-scrubbed.xlMaptitude-FMG-AllGoodEmails-11-4-scrubbe
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.
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
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_scrubbe d.xlsx = a value in Col K of Maptitude_FMG_AllGoodEmail s_11-4_scr ubbed.xlsm paste the value of Col F in MSawyer_10_28_14_Maptitude _D_scrubbe d.xlsx to Col O of MMaptitude_FMG_AllGoodEmai ls_11-4_sc rubbed.xls m.
Col E of MSawyer_10_28_14_Maptitude _D_scrubbe d.xlsx = Single Value (ZIP Code?)
Col K of Maptitude_FMG_AllGoodEmail s_11-4_scr ubbed.xlsm = Multiple values per cell, separated with pipe symbol
Col F in MSawyer_10_28_14_Maptitude _D_scrubbe d.xlsx = Blank
Please explain.
Thanks
Rob H
Also, the files don't appear to agree with your description of the scenario.
Where in Col E of MSawyer_10_28_14_Maptitude
Col E of MSawyer_10_28_14_Maptitude
Col K of Maptitude_FMG_AllGoodEmail
Col F in MSawyer_10_28_14_Maptitude
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
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
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-Map titude-D-s crubbed.xl s]ZIP Data'!$F:$F,MATCH(MID($K2, O$1,5),'[M Sawyer-10- 28-14-Mapt itude-D-sc rubbed.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
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,"
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
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-scrubbe d.xls"
What should I do with this and why is that happening?
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
The uploaded files had only partial or missing extensions so saved locally with xls, maybe your proper files are xlsx or xlsm
Thanks
Rob
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_scrubbe d.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
And as a note, the other files is named: MSawyer_10_28_14_Maptitude
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
true, but I'm only copying one column to start. but it's over 200K rows.
ASKER
Thank you Rob. Worked as advertised. Much appreciated.
=INDEX(result_range,MATCH(
result_range = Col F in MSawyer_10_28_14_Maptitude
lookup_value = the value from Col E of MSawyer_10_28_14_Maptitude
lookup_range = Col K of MSawyer_10_28_14_Maptitude
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