I have 2 spreadsheets that I need to find out if numbers on one are on the other. I have a list of numbers in column A going down to about line 200. I need to find out if any of those are in the second spreadsheet. I cant use Vlookup or anything because they could be anywhere on the spreadsheet. Any ideas? Thanks.
They are different files and the numbers could be anywhere on the second sheet.There are just too many numbers to use the find feature. I basically want the number to change colour or something to say 'yes thats in sheet 2'. Basically the answer is yes to all your questions VoG.
It would be easier (for me!) if both of the sheets were in the same file so please copy the second sheet into the file containing your list of numbers.
Press ALT + F11 to open the Visual Basic Editor, Insert > Module. Then copy and paste in
Sub FindDups() Dim ws1 As Worksheet, ws2 As Worksheet, LastRow As Long, i As Long Dim LastCol As Integer, Found As Range, LookFor As Variant Set ws1 = Sheets("Sheet1") ' change to suit Set ws2 = Sheets("Sheet2") ' change to suit With ws1 LastRow = .Columns(1).Find("*", SearchDirection:=xlPrevious).Row LastCol = .Cells.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column For i = 1 To LastRow LookFor = .Range("A" & i).Value Set Found = ws2.UsedRange.Find(what:=LookFor) If Not Found Is Nothing Then .Cells(i, LastCol + 1).Value = Found.Address(False, False) End If Next i End With End Sub
Change the names of the sheets to suit - in the above, Sheet1 is the sheet with the list of numbers and Sheet2 is the sheet to be searched.
Close the VBE using the X. Tools > Macro > Macros, highlight FindDups and click the run button.
A new column will be added listing the addresses where the number is found. Only the first match will be listed.