Comparing Values In 2 Columns

  • Thread starter Thread starter hilaryj
  • Start date Start date
H

hilaryj

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
 
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








- Show quoted text -
Click to expand...

Cheers Earl,

Worked a treat thanks very much

Jay
 
Back
Top