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

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





"Don Doan" wrote:

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

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


"Don Doan" wrote in message
...
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".

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" wrote:

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


"Don Doan" wrote in message
...
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".

Gives the OP something to play with. <g


"Joel" wrote in message
...
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" wrote:

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


"Don Doan" wrote in message
...
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".

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


"Joel" wrote in message
...
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" wrote:

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


"Don Doan" wrote in message
...
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".

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

"JLGWhiz" wrote:

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


"Joel" wrote in message
...
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" wrote:

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


"Don Doan" wrote in message
...
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".