finding a specific value on a master file

[Don Doan]

Hi there,
Perhaps someone can help me with this.
I have 2 sheets in the same workbook. The first sheet called "Masterfile"
and it has all the security ids ( something like SXSF1001002) in column A.The
second sheet called "List" and it has some securities id also in column A. Is
there a way for me to check each elements in the "List" sheet against the
"Master" sheet? If there is something then say "yes" in column B of the
"List" and if not, then say "no".

 

[Joel]

I had very similar code from a posting this morning.

Sub Duplicates()
ScreenUpdating = False

'get first empty row of sheet1

'find matching rows in sheet 2
With Sheets("Masterfile")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
ID = Trim(.Range("A" & RowCount))
'compare - look for ID in Sheet 2
With Sheets("List")
Set c = .Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
End With
If c Is Nothing Then
.Range("B" & RowCount) = "No"
Else
.Range("B" & RowCount) = "Yes"
End If

RowCount = RowCount + 1
Loop
End With

ScreenUpdating = True

End Sub

 

[JLGWhiz]

This will identify the matched items with a "Yes"
Blanks will indicate no match.

Sub ckList()
Dim lr As Long, sh1 As Worksheet, sh2 As Worksheet
Dim rng As Range
Set sh1 = Sheets("Masterfile")
Set sh2 = Sheets("List")
lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr1
For j = 2 to lr2
If sh2.Cells(j, 1) = Sh1.Cells(i, 1) Then
Sh.Range("B" & j) = "Yes"
End If
Next
Next
End Sub

 

[Joel]

The for loop approach is much slower than using Find. You also aren't
putting th No in column B when it is not found.

 

[JLGWhiz]

Gives the OP something to play with. <g>

The for loop approach is much slower than using Find. You also aren't
putting th No in column B when it is not found.

 

[JLGWhiz]

This should work to include the "No" in col. B.

Sub ckList()
Dim lr As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Masterfile")
Set sh2 = Sheets("List")
lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
For Each c In sh2.Range("A2:A" & lr2)
For i = 1 To lr1
If c = sh1.Cells(i, 1) Then
c.Offset(, 1) = "Yes"
Exit For
Else
c.Offset(, 1) = "No"
End If
Next
Next
End Sub

 

[Don Doan]

Thank you both for helping out....the codes work great!!!!!