Select value, search down a column for match

[rtpeter]

I'm trying to "create a macro" to copy a value (from Column-A), search down Column-C, find matching email and then paste it in Column-B of the same worksheet. Column-C remains unchanged, as it's simply a source of email addresses for possible matches. If no match is found, process continues down Column-A.

BEFORE:
Name,Email,Source_Emails,
Homer.Simpson,,Homer.Simpson@yahoo.com,
Marge.Simpson,,Marge.Simpson@hotmail.com,
Bart.Simpson,,Maggie.Simpson@yahoo.com,
Lisa.Simpson,,Mary.Bailey@yahoo.com,
Maggie.Simpson,,Jasper.Beardly@hotmail.com,

AFTER:
Name,Email,Source_Emails,
Homer.Simpson,Homer.Simpson@yahoo.com,Homer.Simpson@yahoo.com,
Marge.Simpson,Marge.Simpson@hotmail.com,Marge.Simpson@hotmail.com,
Bart.Simpson,,Maggie.Simpson@yahoo.com,
Lisa.Simpson,,Mary.Bailey@yahoo.com,
Maggie.Simpson,Maggie.Simpson@yahoo.com,Jasper.Beardly@hotmail.com,

 

[circledchicken]

Hi,

Can you use a formula or do you need to use a macro?

Perhaps try this formula and drag down (for Excel 2007+):

Excel Workbook
	A	B	C
1	Name	Email	Source_Emails
2	Homer.Simpson	Homer.Simpson@yahoo.com	Homer.Simpson@yahoo.com
3	Marge.Simpson	Marge.Simpson@hotmail.com	Marge.Simpson@hotmail.com
4	Bart.Simpson		Maggie.Simpson@yahoo.com
5	Lisa.Simpson		Mary.Bailey@yahoo.com
6	Maggie.Simpson	Maggie.Simpson@yahoo.com	Jasper.Beardly@hotmail.com
Sheet2

Excel 2010

Cell Formulas
Range	Formula
B2	=IFERROR(INDEX(C$2:C$6,MATCH(A2,INDEX(LEFT(C$2:C$6,FIND("@",C$2:C$6)-1),0),0)),"")

Or for earlier versions maybe try:

=IF(ISERROR(MATCH(A2,INDEX(LEFT(C$2:C$6,FIND("@",C$2:C$6)-1),0),0)),"",INDEX(C$2:C$6,MATCH(A2,INDEX(LEFT(C$2:C$6,FIND("@",C$2:C$6)-1),0),0)))

 

[HalfAce]

And if you plan to use vba instead of the formula approach, perhaps something like this.

Sub TheSimpsonsMacro()
Dim RngA As Range, RngC As Range, LstRw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row
For Each RngA In Range("A1:A" & LstRw)
  For Each RngC In Range("C1:C" & LstRw)
    If RngA.Value = Left(RngC.Value, Len(RngA.Value)) Then _
      RngA(, 2).Value = RngC.Value
  Next RngC
Next RngA
End Sub

Hope it helps.

 

[rtpeter]

CircledChicken, HalfAce,

Thank you both so much for your help. Both suggestions worked great on my example file, but the formula also worked extremely well on my real data. Would either of you recommend a good macros and formulas book for an occasional user?

Thank you again for such quick and thorough responses!
V/R Richard

 

[rtpeter]

To be clear, the response from HalfAce worked perfect.

Thank you again,
Richard

 

[circledchicken]

Your welcome and thanks for your feedback.

I'm not sure about books but here are a few of the online resources that I use and have helped me a lot:

Videos for all levels and great explanation of everything (including some VBA I think):
http://www.youtube.com/user/ExcelIsFun
http://www.youtube.com/user/bjele123
http://www.youtube.com/user/contextures

Websites:
http://www.cpearson.com/Excel/MainPage.aspx
http://contextures.com/tiptech.html
http://peltiertech.com/
http://www.ozgrid.com

I know there are many, many more great ones but I can't list them all...