Retrieve email addresses from another worksheet

[cjGod]

Hi everyone,

Attached is the excel file example
Need help in retrieving email address from Column M in "Work1" into Column E in "Sheet1" by matching the Keys in "Sheet1" against the Keys in "Column D - Work1".
The email in the same row will be retrieved.

There are duplications of the same Keys. E.g. 4 Donut in "Sheet1". In "Work1", there will also be the same 4 duplications of Donut.


Example of the output in "Sheet1" (After retrieving the email addresses from Work1) will be:

Donut [email protected]
Donut [email protected]
Donut [email protected]
Donut [email protected]



Thanks!

[watersev]

hi, cjGod, solution with VB, please check attachment, run code "test".

Notes: As you have "Donut" on the first sheet and "Donuts" on the "Work1" sheet, the search is made by part match.

[cjGod]

Hi watersev,


I have amended the Keys to be the same in work1 and sheet1. Please find attached.

Thanks for macros, however it is not exactly solving. I need to check for all the Keys in sheet1 against columns D to J in Work1 and not just in column D itself.

Also there might possibility that I will add in new Keys in sheet1 and the keys will be in new columns in work1. If it is possible to do something dynamic, that I can edit the macro VB script next time if I have new Keys added.



Thanks!

[watersev]

Need help in retrieving email address from Column M in "Work1" into Column E in "Sheet1" by matching the Keys in "Sheet1" against the Keys in "Column D - Work1".The email in the same row will be retrieved.

ok, solution provided as per initial task. The code does exactly what was expected as per description.

Thanks for macros, however it is not exactly solving. I need to check for all the Keys in sheet1 against columns D to J in Work1
Thanks!

It would be correctly to say the code does exactly solving the problem set. I'm not an extrasens to guess that "matching the Keys in "Sheet1" against the Keys in "Column D - Work1" means "check for all the Keys in sheet1 against columns D to J in Work1".

[watersev]

please check attachment, run code "test". Input keys data range last column index number.

Notes:
1. Option Compare Text makes "A" equal to "a" so it turns off case sensitivity. If you do not need it, delete this line.

[snb]

or

Please Login or Register  to view this content.

[cjGod]

please check attachment, run code "test". Input keys data range last column index number.

Notes:
1. Option Compare Text makes "A" equal to "a" so it turns off case sensitivity. If you do not need it, delete this line.

Hi watersev,

It is working Great!. Many thanks for your help

[watersev]

if you are happy with the offered solution, please mark the thread as solved (see Forum rules for details)

[cjGod]

if you are happy with the offered solution, please mark the thread as solved (see Forum rules for details)

Hi watersev,


The macros in your latest workbook, actually put the email address by matching the Keys. I have another problem here which in the event in "Sheet1" the key is "hotline" and in "Work1" there is 1 key which is "hotline check", it will still put the email.
I need the macros to match the exact Keys, example the email will be only pulled if in "Sheet1" the key is "hotline", in "Work1" the key need to be "hotline" also.



Regards
CJ

[watersev]

change this line:

Please Login or Register  to view this content.

to this one:

Please Login or Register  to view this content.

[watersev]

please check attachment