Comparing two data sets

**Jietoh** · 04-01-2014, 01:45 PM

Hello! I'm trying to compare a data set against the master list to find a match. Once a match is found I want to copy the data in the adjacent columns back to my original list. Here is my code so far.

Sub Find_Specific_Boxes()
Worksheets("Full List").Select
Range("E2").Select
Worksheets("Other").Select
Range("A1").Select

Dim Count As Long
Dim x As Integer
x = ActiveSheet.UsedRange.Rows.Count

For Count = 1 To x

    If Worksheets("Other").ActiveCell = Worksheets("Full List").ActiveCell = True Then
        Worksheets("Full List").ActiveCell.Offset(0, -4).Select
        Selection.Copy
        Worksheets("Other").ActiveCell.Offset(0, 1).Paste
        GoTo Continue
    Else
        Worksheets("Full List").ActiveCell.Offset(1, 0).Select
        GoTo Continue
    End If
Continue:
    Next Count
    
Range("E2").Select
End Sub

The data being compared begins in "A1" (on my data set) and "E2" (in my master list). If A1 doesn't match E2 I want to check it against E3 and so on until a match is found. Once the match is made I want to select columns A - D of my master list and paste it next to the corresponding match in my data set. Hopefully this all makes sense. Thanks

**xladept** · 04-01-2014, 06:26 PM

Hi,

I may have gotten (Have gotten?) confused - try this:

Sub Find_Specific_Boxes() 
Dim wf As Worksheet, wo As Worksheet
Set wf = Worksheets("Full List")
Set wo = Worksheets("Other")

Dim Count As Long
Dim x As Integer, y As Integer: y = 2
x = ActiveSheet.UsedRange.Rows.Count

For Count = 1 To x
Continue:
    If wo.Range("A" & Count) = wf.Range("E" & y) Then
        wf.Range("A" & y).Copy wo.Range("B" & x)
        
    Else: y = y + 1: GoTo Continue
    End If
    y = 2: Next Count
    
wf.Range("E2").Select
End Sub