I need to compare 2 large lists of numbers and out put numbers that are in both lists.

[imcquill09]

Hi everyone thanks for taking the time to help out.

I have two databases in excel I need to compare. One contains the serial numbers of a bunch of items that are assigned to my organization. The other contains the serial numbers of all the items in my organization that are registered for maintenance. Over time these two lists no longer match.

I can easily put all the data in one workbook in two separate columns. What I want to do is run a macro that can compare these to lists and give three outputs which are serial numbers that are:

1) Only in list A.
2) Only in list B.
3) In both lists A and B.

Thanks for the help!

[PCI]

To help to prepare the macro and be sure to handle the data at the right place, it will very good to prepare an Excel sample of the data showing how, where the final result stands: Attach the sample in the thread

[JOHN H. DAVIS]

Maybe:

Sub imcquill09()
Dim i As Long, x As Range, y As Long
y = Range("B" & Rows.Count).End(3).row
For i = 2 To Range("A" & Rows.Count).End(3).row
    Set x = Columns(2).Find(Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
        If Not x Is Nothing Then
            Cells(Rows.Count, "E").End(3)(2) = Cells(i, "A")
            x.Interior.ColorIndex = 6
        Else
            Cells(Rows.Count, "C").End(3)(2) = Cells(i, "A")
        End If
    Set x = Nothing
Next i
For Each x In Range(Cells(2, 2), Cells(y, 2))
    If x.Interior.ColorIndex = xlNone Then
        x.Copy Range("D" & Rows.Count).End(3)(2)
    Else
       x.Interior.ColorIndex = xlNone
    End If
Next x
            
End Sub

Comparing Columns A and B.
Output 1) - Column C
Output 2) - Column D
Output 3) - Column E

[Arkadi]

This ought to do the trick... you will need to play with the columns probably... this assumes source sheet has list A in col A, and list B on Col B of "Sheet1"
It then outputs Only in A in col A, only in B in col B, and what is in both in col C. Output sheet is "Sheet2"

Sub compare()

Dim arr As Variant
Dim varr As Variant
Dim wss As Worksheet
Dim wsd As Worksheet
Dim x, y
Dim match As Boolean
Dim i As Long, j As Long

Application.ScreenUpdating = False

Set wss = Worksheets("Sheet1") 'sheet with source data
Set wsd = Worksheets("Sheet2") 'sheet with output columns

arr = wss.Range("A2:A" & wss.Range("A" & Rows.Count).End(xlUp).Row).Value 'starts at A2 assuming a header
varr = wss.Range("B2:B" & wss.Range("B" & Rows.Count).End(xlUp).Row).Value 'starts at B2 assuming a header
i = 1 'will start the list of values only in A and only in B on row 2 because later we add 1 before writing
j = 1 'will start the list of values that are in both lists on row 2 because later we add 1 before writing
'here we compare lists, write what is only in column A, and what is common
For Each x In arr
    match = False
    For Each y In varr
        If x = y Then 'there is a match in col A vs Col B
            match = True
            j = j + 1
            wsd.Range("C" & j).Value = x 'write the value of X in the list of common values
        End If
    Next y
    If Not match Then
        i = i + 1
        wsd.Range("A" & i).Value = x 'write the value in the list of items in A but not in B
    End If
Next
'now get the list only in B
i = 1
For Each x In varr
    match = False
    For Each y In arr
        If x = y Then match = True
    Next y
    If Not match Then
        i = i + 1
        wsd.Range("B" & i).Value = x 'write the value in the list of items in A but not in B
    End If
Next

Application.ScreenUpdating = True
End Sub