=IF(COUNTIF(Database!$A$2:
Sub x()
Dim r As Range
Set r = Sheets("Database").Range("A1").CurrentRegion
With Sheets("List")
With .Range("C2", .Range("C" & Rows.Count).End(xlUp))
.Formula = "=IF(COUNTIF(Database!" & r.Address & ",B2)>0,""Y"",""N"")"
.Value = .Value
End With
End With
End Sub
Sub x()
Dim rFind As Range, r As Range, rData As Range
Application.ScreenUpdating = False
Set rData = Sheets("Database").Range("A1").CurrentRegion
With Sheets("List")
For Each r In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
Set rFind = rData.Find(What:=r, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If rFind Is Nothing Then
r.Offset(, 1) = "N"
Else
r.Offset(, 1) = "Y"
End If
Next r
End With
Application.ScreenUpdating = True
End Sub
Sub x()
Dim oDic As Object, vData As Variant, i As Long, v, vOut(), j As Long
Set oDic = CreateObject("Scripting.Dictionary")
vData = Sheets("Database").Range("A1").CurrentRegion.Value
v = Sheets("List").Range("B2", Sheets("List").Range("B" & Rows.Count).End(xlUp))
ReDim vOut(1 To UBound(v, 1))
With oDic
For j = LBound(vData, 2) To UBound(vData, 2)
For i = LBound(vData, 1) To UBound(vData, 1)
If Not IsEmpty(vData(i, j)) And Not .Exists(vData(i, j)) Then
.Add vData(i, j), vData(i, j)
End If
Next i
Next j
For i = LBound(v, 1) To UBound(v, 1)
If .Exists(v(i, 1)) Then
vOut(i) = "Y"
Else
vOut(i) = "N"
End If
Next i
End With
Sheets("List").Range("C2").Resize(i - 1) = Application.Transpose(vOut)
End Sub
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
7 Experts available now in Live!