Sample matching search excel - help

[Zedstar]

Hi,

Hope someone can help with the following:

I have a spreadsheet with a 10,000 names and another with 200 names.

I wish to find out whether any of these 200 names appear in the spreadsheet with the 10,000 names and have the lines with any associated data to these names highlighted/selected.

I also would need to repeat that proces with new short lists every other day so is there anyways of automating this?

How does one go about it efficiently?

Many thanks to anyone who reads this and are able to help.

ZE

 

[venkat1926]

first try this macro
data in sheet1 is hving 10000 name and sheet 2 200 names
in each sheet row no.1 has column headings.

Dim rng1 As Range, rng2 As Range
Dim c As Range
Dim cfind As Range
Dim x
With Worksheets("sheet2")
Set rng2 = Range(.Range("a2"), .Range("a2").End(xlDown))
End With
With Worksheets("sheet1")
Set rng1 = Range(.Range("a2"), .Range("a2").End(xlDown))
End With
With Worksheets("sheet2")
For Each c In rng2
x = c.Value
    With Worksheets("sheet1")
    On Error Resume Next
    Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
    If cfind Is Nothing Then GoTo line1
    Range(cfind, cfind.End(xlToRight)).Interior.ColorIndex = 3
        End With 'sheet1
line1:
    Next
End With 'sheet2
End Sub

 

[jindon]

try
Change red part as required.

Sub test()
Dim a, e, i As Long
With Sheets("SheetWith10000Names")  '<- change the sheet name
    a = .Range("a2", .Range("a" & Rows.Count).End(xlUp)).Value
End With
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each e In a
        If (Not IsEmpty(e)) * (Not .exists(e)) Then .add e, Nothing
    Next
    With Sheets("SheetWithShortList") '<- change the sheet name
        a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Value
    End With
    For i = 2 To UBound(a,1)
        If ,exists(a(i,1)) Then txt = txt & "," & Cells(i,1).Resize(,2).Address(0,0)
        If Len(txt) > 245 Then
            Sheets("SheetWithShortList").Range(Mid$(txt,2)).Interior.Color = vbRed
            txt = ""
        End If
    Next
End With
If Len(txt) Then Sheets("SheetWithShortList").Range(Mid$(txt,2)).Interior.Color = vbRed
End Sub

 

[Zedstar]

For the love of God, i hope there is a seat reserved for you in heaven.

Thanks for that.