Hi, i have 2 list of students names both of which are not up to date
with each other and because of this one list has more students than
the other. I want to search for students names and see if there is a
match, if a match is found i need to copy the email address and paste
it into the cell by the other name in the other list. See Below

My spreadsheet has columns titled, (A)Display Name, (B)Display Email,
(C)Sims Name and (D)Sims Email. I basically want to write a script
that takes each individual display name in column A and searches in
column C for an identical match, if a match is found i then need it to
look at column D and if an email address is present copy it into
column B on the row that the display name is on? Is there a quick
method?

Thanks In Advance

Jay

Have a look in the vba help index for FINDNEXT. There is a good example.

--
Don Guillett
SalesAid Software
(E-Mail Removed)
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi, i have 2 list of students names both of which are not up to date
> with each other and because of this one list has more students than
> the other. I want to search for students names and see if there is a
> match, if a match is found i need to copy the email address and paste
> it into the cell by the other name in the other list. See Below
>
> My spreadsheet has columns titled, (A)Display Name, (B)Display Email,
> (C)Sims Name and (D)Sims Email. I basically want to write a script
> that takes each individual display name in column A and searches in
> column C for an identical match, if a match is found i then need it to
> look at column D and if an email address is present copy it into
> column B on the row that the display name is on? Is there a quick
> method?
>
> Thanks In Advance
>
> Jay
>

Jay,

Here's a formula way. Put this in a column (perhaps E), and copy down with the Fill Handle.
=IF(ISERROR(VLOOKUP(A2,$C$2:$D$13,2,FALSE)),"",VLOOKUP(A2,$C$2:$D$13,2,FALSE))

If you want it to return the email address in column B for entries in column A that don't
have a match in column C, use

=IF(ISERROR(VLOOKUP(A2,$C$2:$D$13,2,FALSE)),B2,VLOOKUP(A2,$C$2:$D$13,2,FALSE))

If there are already some email addresses in column B, then this will have to be combined
with column B with another formula.

You asked for a script. Here's a macro. It will overlay any email in B if it finds a match
in D

Sub CombineLists2()
Dim ARow As Long
Dim CRow As Long
Dim CellA As Range, ColA As Range
Dim CellC As Range, ColC As Range
ARow = 2 ' starting row
CRow = 2
Set ColA = Range(Cells(ARow, 1), Cells(ARow, 1).End(xlDown))
Set ColC = Range(Cells(CRow, 3), Cells(CRow, 3).End(xlDown))
For Each CellA In ColA
CRow = 2
For Each CellC In ColC
If CellA = CellC Then ' match name?
CellA.Offset(0, 1) = CellC.Offset(0, 1) ' put email address in B
End If
Next CellC
Next CellA
End Sub

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi, i have 2 list of students names both of which are not up to date
> with each other and because of this one list has more students than
> the other. I want to search for students names and see if there is a
> match, if a match is found i need to copy the email address and paste
> it into the cell by the other name in the other list. See Below
>
> My spreadsheet has columns titled, (A)Display Name, (B)Display Email,
> (C)Sims Name and (D)Sims Email. I basically want to write a script
> that takes each individual display name in column A and searches in
> column C for an identical match, if a match is found i then need it to
> look at column D and if an email address is present copy it into
> column B on the row that the display name is on? Is there a quick
> method?
>
> Thanks In Advance
>
> Jay
>

On 8 Feb, 15:32, "Earl Kiosterud" <some...@nowhere.com> wrote:
> Jay,
>
> Here's a formula way. Put this in a column (perhaps E), and copy down with the Fill Handle.
> =IF(ISERROR(VLOOKUP(A2,$C$2:$D$13,2,FALSE)),"",VLOOKUP(A2,$C$2:$D$13,2,FALS*E))
>
> If you want it to return the email address in column B for entries in column A that don't
> have a match in column C, use
>
> =IF(ISERROR(VLOOKUP(A2,$C$2:$D$13,2,FALSE)),B2,VLOOKUP(A2,$C$2:$D$13,2,FALS*E))
>
> If there are already some email addresses in column B, then this will have to be combined
> with column B with another formula.
>
> You asked for a script. Here's a macro. It will overlay any email in B if it finds a match
> in D
>
> Sub CombineLists2()
> Dim ARow As Long
> Dim CRow As Long
> Dim CellA As Range, ColA As Range
> Dim CellC As Range, ColC As Range
> ARow = 2 ' starting row
> CRow = 2
> Set ColA = Range(Cells(ARow, 1), Cells(ARow, 1).End(xlDown))
> Set ColC = Range(Cells(CRow, 3), Cells(CRow, 3).End(xlDown))
> For Each CellA In ColA
> CRow = 2
> For Each CellC In ColC
> If CellA = CellC Then ' match name?
> CellA.Offset(0, 1) = CellC.Offset(0, 1) ' put email address in B
> End If
> Next CellC
> Next CellA
> End Sub
>
> --
> Earl Kiosterudwww.smokeylake.com
> -----------------------------------------------------------------------<hila...@st-albans.suffolk.sch.uk> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > Hi, i have 2 list of students names both of which are not up to date
> > with each other and because of this one list has more students than
> > the other. I want to search for students names and see if there is a
> > match, if a match is found i need to copy the email address and paste
> > it into the cell by the other name in the other list. See Below
>
> > My spreadsheet has columns titled, (A)Display Name, (B)Display Email,
> > (C)Sims Name and (D)Sims Email. I basically want to write a script
> > that takes each individual display name in column A and searches in
> > column C for an identical match, if a match is found i then need it to
> > look at column D and if an email address is present copy it into
> > column B on the row that the display name is on? Is there a quick
> > method?
>
> > Thanks In Advance
>
> > Jay- Hide quoted text -
>
> - Show quoted text -

Cheers Earl,

Worked a treat thanks very much

Jay