Search, Copy and Paste

Hello japorms,

How do you want to handle duplicate data?

Sincerely,
Leith Ross

Originally Posted by Leith Ross

Hello japorms,

How do you want to handle duplicate data?

Sincerely,
Leith Ross

it can be deleted.

Hello japorms,

Sorry for the delay, my computer decided some of my software needed to updated. I added a button on Sheet3 that will perform the copy paste. Only the unique entries on Sheet1 are copied to Sheet3. A search is done on Sheet2 for each of these entries. When a match is found, the data from column "B" of the search result is transferred to column "B" of Sheet3 in the same row of that search term. This macro has already been added.

Sub Macro1()

  Dim DstWks As Worksheet
  Dim I As Long
  Dim LastRow As Long
  Dim R As Long
  Dim Rng As Range
  
    Set DstWks = Worksheets("Sheet3")
    
   'Clear previously stored data on the destination worksheet
    DstWks.UsedRange.Clear
    
   'Copy only unique values to the destination worksheet
    With Worksheets("Sheet1")
      LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
      Set Rng = .Range(.Cells(1, "A"), .Cells(LastRow, "A"))
      Rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
      Rng.Copy Destination:=DstWks.Range("A1")
      .ShowAllData
    End With
    
   'Get the last row that has data on the destination worksheet
    With DstWks
      LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
   'Search Sheet2 using the entries from the destination worksheet
    With Worksheets("Sheet2")
      For R = 1 To LastRow
        Set Results = .Cells.Find(What:=DstWks.Cells(R, 1), _
                                  After:=.Cells(1, 1), _
                                  LookIn:=xlValues, _
                                  LookAt:=xlWhole, _
                                  SearchOrder:=xlByRows, _
                                  searchDirection:=xlNext, _
                                  MatchCase:=False)
       'If a match if found, copy the contents of column "B" of Sheet2 and
       'paste it into column "B" of the destination worksheet
        If Not Results Is Nothing Then DstWks.Cells(R, 2) = Results.Offset(0, 1)
      Next R
    End With
    
End Sub

Sincerely,
Leith Ross

Thanks for the code but it didn't really answer my needs. I decided to use access to combine two data from excel. Thanks!