Excel Question

  steviegee 09:12 07 Feb 2008
Locked

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.

  johnnyrocker 09:19 07 Feb 2008

what about 'find' feature?


johnny.

  VoG II 09:25 07 Feb 2008

Are these two sheets in the same file or different files?

On the second sheet, could the number be in any column? How many columns are there? Could the number occur more than once and if so are you interested in knowing about that?

  steviegee 09:37 07 Feb 2008

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.

  VoG II 10:22 07 Feb 2008

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.

  steviegee 12:16 07 Feb 2008

You are a genius! How on earth did you work that out? Thanks.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone