View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Spiggy Topes Spiggy Topes is offline
external usenet poster
 
Posts: 22
Default Create list of duplicated numbers

Another approach would be to clone the ranges to a separate sheet,
sort both lists into ascending order, then a single pass through the
sheet as follows:

Option Explicit
Sub doit()
Dim i As Integer
Dim j As Integer
Dim k As Integer

i = 1
j = 1
k = 1

Do While i <= ActiveSheet.UsedRange.Rows.Count And j <=
ActiveSheet.UsedRange.Rows.Count
Select Case Cells(i, 1) - Cells(j, 2)
Case Is < 0
i = i + 1
Case Is 0
j = j + 1
Case Else
Cells(k, 3) = Cells(i, 1)
i = i + 1
j = j + 1
k = k + 1
End Select
Loop
End Sub

I leave you to fill in the clone process, adjust column numbers,
identify the sheet for the destination cell and delete the cloned
sheet once you're done. Note that you didn't specify what to do with
multiple occurrences in each column; if the number 17, say, occurs
once in one column and three times in the other, this code will
identify one match only; if the same number occurs three times in each
column, then you'll get three matches. Easy enough to tweak to
eliminate duplicate matches if needed.