Comparing 2 columns of numbers for differences

[silverlucky5]

Hi,
I have two lists of numbers that are supposed to be identical but are not.
Some numbers are in col 1 and not in col 2.
Some numbers are in Col 2 and not in Col 1. (see sample).
With my sample, since it is very small, it is easy enough to compare the two columns to see the differences.
But the columns I am actually comparing are thousands of rows long (and unlike the sample below, in my actual data, the "matches" are not aligned.).

Is there any way to write a macro to do this compare and tell me which numbers are in col 1 and not in col 2 and which numbers are in col 2 and not in col 1?

Thanks for any help!

<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; width: 48pt;" height="17" width="64">col 1</td> <td class="xl25" style="border-left: medium none; width: 48pt;" width="64">col 2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">1234</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">1234</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">5678</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">5678</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">10122</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">10122</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">14566</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">14566</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">19010</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">19010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">23454</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">27898</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">32342</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">32342</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">36786</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">36786</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">41230</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">41230</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">45674</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">45674</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">50118</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">54562</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">54562</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">59006</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">59006</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">63450</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">63450</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">67894</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">67894</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">72338</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">81226</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">81226</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">85670</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">85670</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">72338</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">76782</td> </tr> </tbody></table>

 

[silverlucky5]

Also, forgot to add one very important point. I want the two lists to align as shown in the sample.

<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td colspan="2" class="xl26" style="border-right: 0.5pt solid black; height: 12.75pt; width: 96pt;" height="17" width="128">BEFORE</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" height="17">col 1</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">col 2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">1234</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">1234</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">5678</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">5678</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">10122</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">10122</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">14566</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">14566</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">19010</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">19010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">23454</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">32342</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">27898</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">36786</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">32342</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">41230</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">36786</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">45674</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">41230</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">54562</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">45674</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">59006</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">50118</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">63450</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">54562</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">67894</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">59006</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">72338</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">63450</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">81226</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">67894</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">85670</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">81226</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">72338</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">85670</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">76782</td> </tr> </tbody></table>
<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td colspan="2" class="xl25" style="height: 12.75pt; width: 96pt;" height="17" width="128">AFTER (same as above sample)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; border-top: medium none;" height="17">col 1</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">col 2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">1234</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">1234</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">5678</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">5678</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">10122</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">10122</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">14566</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">14566</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">19010</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">19010</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">23454</td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">27898</td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">32342</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">32342</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">36786</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">36786</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">41230</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">41230</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">45674</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">45674</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">50118</td> <td class="xl24" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">54562</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">54562</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">59006</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">59006</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">63450</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">63450</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">67894</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">67894</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">72338</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">81226</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">81226</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" align="right" height="17">85670</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">85670</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">72338</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;" align="right">76782</td> </tr> </tbody></table>

 

[texasalynn]

ok so here is what I came up with and it isn't exactly what you wanted

Sub AlignItems()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("A:A").Copy Destination:=Sheets("Sheet2").Range("A1")
For i = 2 To LR
        IFind = Cells(i, 2)
        Sheets("Sheet2").Activate
            
    Set rFound = Columns(1).Find(What:=IFind, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False _
        , SearchFormat:=False)
        If Not rFound Is Nothing Then
            rFound.Activate
            rFound.Offset(0, 1) = IFind
        End If
         If rFound Is Nothing Then
            Cells(LR + 1, 1) = IFind
            Cells(LR + 1, 1).Font.Bold = True
            Cells(LR + 1, 1).Font.ColorIndex = 3
            LR = Range("A" & Rows.Count).End(xlUp).Row
        End If
        Sheets("Sheet3").Activate
       
Next i
MsgBox "Done"
Application.ScreenUpdating = True
End Sub

on the sheet2 this code will put the numbers not found at the bottom of column a as bold and red

 

[silverlucky5]

Thank you texasalynn! I will test out and post back with results when done..

 

[ArfArfArf]

You might consider using the CountIf command. This way needs you to have columns C and D free for adding some formulas... the code is as follows. I copied your sample data into a new sheet, so headers are in A1 and B1..

Sub CountIfVBA()

Range("C1").Select
ActiveCell.FormulaR1C1 = "Col2 in Col 1"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-2],RC[-1])"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C25"), Type:=xlFillDefault

Range("D1").Select
ActiveCell.FormulaR1C1 = "Col1 in Col 2"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-2],RC[-3])"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D25"), Type:=xlFillDefault

End Sub

What this does is as follows;
Column C tells you if values from Column A appear in Column B.
e.g. so C2 tells you that the value in B2 appears somewhere in Column A

..and D2 tells you that the value in A2 appears somewhere in column B (if it has value = 1)

you can use other code to tell vba how many rows to copy this down, i.e. replace Selection.AutoFill Destination:=Range("D2:D25") with

Selection.AutoFill Destination:=range(strRange) by working out what the last line ought to be and creating strRange to cover the right length

 

[silverlucky5]

Thanks so much for the reply Arf....what you did is cool, but doesn't quite get me to where I want to be. Here is a link that shows what I'm doing manually. I hope it explains better than my words as to what I'm trying to accomplish. Sorry...there is no sound, just the video...
Thanks again for your post...

http://www.screencast.com/t/8JrlBxCh

 

[JoeMo]

Not sure if this is what you want. Compares two lists and returns differences as well as (optionally) matches.

Option Base 1
Sub CompareTwoLists()

'Purpose is to compare two lists and identify differences
'between them. Specifically, identify items in list1 that are
'not in list2 and vice-versa.

Dim Rng As Range, rList1 As Range, rList2 As Range, cel As Range
Dim msg As String, unMatched1 As String, unMatched2 As String
Dim ctr1 As Long, ctr2 As Long, i As Long, j As Long, k As Long
Dim aList1(), aList2(), aComList(), comCtr As Long, test As Long
Dim rOutput1 As Range, rOutput2 As Range, rOutputCom As Range
msg = "To compare two lists, first use your mouse to select "
msg = msg & "the first list. Then, hold down the control key "
msg = msg & "and select the second list. Then click OK." & vbCrLf & vbCrLf
msg = msg & "NOTE: THIS COMPARISON IS NOT CASE SENSITIVE."

Application.ScreenUpdating = True   'Need for inputbox
Application.Calculation = xlCalculationManual

On Error Resume Next
Set Rng = Application.InputBox(prompt:=msg, Type:=8, Title:="COMPARE TWO LISTS")
If Err.Number <> 0 Then Exit Sub 'Cancel was clicked
On Error GoTo 0
If Rng.Areas.Count <> 2 Then
    msg = "You must select two ranges and only two. Try again."
    MsgBox msg
    Exit Sub
End If
Application.ScreenUpdating = False
Set rList1 = Rng.Areas(1)
Set rList2 = Rng.Areas(2)
'First, compare list1 to list2 and single out items NOT in
'list2.

For Each cel In rList1
    On Error Resume Next
    test = WorksheetFunction.Match(cel.Value, rList2, 0)
    If Err.Number <> 0 Then 'there was no match
        unMatched1 = unMatched1 & "; " & cel.Value
        ctr1 = ctr1 + 1
        ReDim Preserve aList1(ctr1)
        aList1(ctr1) = cel.Value
    Else  'There is a match
        comCtr = comCtr + 1
        ReDim Preserve aComList(comCtr)
        aComList(comCtr) = cel.Value
    End If
Next cel
On Error GoTo 0

If ctr1 > 0 Then
    msg = "There are " & ctr1 & " items in List1 that are not in List2." & vbCrLf & vbCrLf
    MsgBox msg & Right(unMatched1, Len(unMatched1) - 1)
    
    Application.ScreenUpdating = True
    msg = "If you want these items placed in a separate list, select a cell to begin the list." & vbCrLf & vbCrLf
    msg = msg & "Otherwise, click Cancel."
    On Error Resume Next
    Set rOutput1 = Application.InputBox(prompt:=msg, Type:=8, Title:="LIST ITEMS FROM LIST1 THAT HAVE NO MATCH IN LIST2")
    If Err.Number = 0 Then
    Application.ScreenUpdating = False
        For i = 1 To UBound(aList1)
            rOutput1.Offset(i - 1, 0).Value = aList1(i)
        Next i
        With rOutput1.Offset(-1, 0)
            .Value = "Unique to List1"
            .Font = "arial narrow"
            .Font.Size = 10
            .Font.Underline = True
            .Font.Bold = True
        End With
        Range(rOutput1.Offset(-1, 0), rOutput1.Offset(-1, 0).End(xlDown)).Columns.AutoFit
    End If
    Application.ScreenUpdating = True
Else   'ctr1 =0
    msg = "There are no items in List1 that are not in List2."
    MsgBox msg
End If
On Error GoTo 0
'Now compare list2 to list1 and single out items from list2
'that are not in list1.

For Each cel In rList2
    On Error Resume Next
    test = WorksheetFunction.Match(cel.Value, rList1, 0)
    If Err.Number <> 0 Then 'there was no match
        unMatched2 = unMatched2 & "; " & cel.Value
        ctr2 = ctr2 + 1
        ReDim Preserve aList2(ctr2)
        aList2(ctr2) = cel.Value
    End If
Next cel
On Error GoTo 0

If ctr2 > 0 Then
    msg = "There are " & ctr2 & " items in List2 that are not in List1." & vbCrLf & vbCrLf
    MsgBox msg & Right(unMatched2, Len(unMatched2) - 1)
    
    Application.ScreenUpdating = True
    msg = "If you want these items placed in a separate list, select a cell to begin the list." & vbCrLf & vbCrLf
    msg = msg & "Otherwise, click Cancel."
    
    On Error Resume Next
    Set rOutput2 = Application.InputBox(prompt:=msg, Type:=8, Title:="LIST ITEMS FROM LIST2 THAT HAVE NO MATCH IN LIST1")
    If Err.Number = 0 Then
    Application.ScreenUpdating = False
        For j = 1 To UBound(aList2)
            rOutput2.Offset(j - 1, 0).Value = aList2(j)
        Next j
        With rOutput2.Offset(-1, 0)
            .Value = "Unique to List2"
            .Font = "arial narrow"
            .Font.Size = 10
            .Font.Underline = True
            .Font.Bold = True
        End With
        Range(rOutput2.Offset(-1, 0), rOutput2.Offset(-1, 0).End(xlDown)).Columns.AutoFit
    
    End If
Else   'ctr2 =0
    msg = "There are no items in List2 that are not in List1."
    MsgBox msg

End If

'Optionally, list common items if there are a large number
'of unique items between the two lists.
If comCtr > 0 Then
    Application.ScreenUpdating = True
    msg = "There are " & comCtr & " COMMON ITEMS among the two lists." & vbCrLf
    msg = msg & "Select a cell if you want to list them, otherwise click Cancel."
    On Error Resume Next
    Set rOutputCom = Application.InputBox(prompt:=msg, Type:=8, Title:="LIST COMMON ITEMS")
    If Err.Number = 0 Then
    Application.ScreenUpdating = False
        For k = 1 To UBound(aComList)
            rOutputCom.Offset(k - 1, 0).Value = aComList(k)
        Next k
        With rOutputCom.Offset(-1, 0)
            .Value = "Common to Both Lists"
            .Font = "arial narrow"
            .Font.Size = 10
            .Font.Underline = True
            .Font.Bold = True
        End With
        Range(rOutputCom.Offset(-1, 0), rOutputCom.Offset(-1, 0).End(xlDown)).Columns.AutoFit
    
    End If
End If
Rng.Cells(1, 1).Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[silverlucky5]

Wow Joe, this novice is very impressed! If you click on the link I provided in the previous post, you'll see how I was torturing myself! lol....

What you've provided, while not creating the final list as shown via the link, is still extremely useful. thanks so much...I appreciate your response (and everyone else's too) very much! I will play around with the ideas and concepts I've learned....

 

[MickG]

Try this
Rearranged data in columns "C & D" and Unique values in column "E" and Value related Columns in Column "F".

[COLOR="Navy"]Sub[/COLOR] MG15Apr35
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] oVal [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] col = 1 To 2
[COLOR="Navy"]Set[/COLOR] Rng = Range(Cells(2, col), Cells(rows.Count, col).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
      [COLOR="Navy"]If[/COLOR] Dn.value <> "" And Not .Exists(Dn.value) [COLOR="Navy"]Then[/COLOR]
            .Add Dn.value, "Column " & col
         [COLOR="Navy"]Else[/COLOR]
             [COLOR="Navy"]If[/COLOR] Not .Item(Dn.value) = "Column " & col [COLOR="Navy"]Then[/COLOR]
                .Remove Dn.value
                [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
     [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Next[/COLOR] col
Range("E1").Resize(.Count, 2) = Application.Transpose(Array(.keys, .Items))
[COLOR="Navy"]End[/COLOR] With
'[COLOR="Green"][B]====================[/B][/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Rng = Rng.Resize(Rng.Count * 2)
ReDim Ray(1 To rows.Count, 1 To 2)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
      [COLOR="Navy"]If[/COLOR] Dn.value <> "" [COLOR="Navy"]Then[/COLOR]
         [COLOR="Navy"]For[/COLOR] col = 1 To 2
            oVal = IIf(col = 1, Dn, Dn.Offset(, 1))
            [COLOR="Navy"]If[/COLOR] Not .Exists(oVal) [COLOR="Navy"]Then[/COLOR]
                n = n + 1
                num = n
                .Add oVal, Array(num, col, 0)
                Ray(n, col) = oVal
            [COLOR="Navy"]Else[/COLOR]
                Q = .Item(oVal)
                [COLOR="Navy"]If[/COLOR] Not Q(1) = col [COLOR="Navy"]Then[/COLOR]
                    Ray(Q(0), col) = oVal
                [COLOR="Navy"]Else[/COLOR]
                    n = n + 1
                       [COLOR="Navy"]If[/COLOR] Q(2) = 0 [COLOR="Navy"]Then[/COLOR]
                            Ray(n, col) = oVal
                            Q(0) = Q(0) & "," & n
                            Q(2) = Q(2) + 1
                      [COLOR="Navy"]Else[/COLOR]
                            Ray(n, Split(Q(0))(Q(2))) = oVal
                      [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]End[/COLOR] If
       [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] col
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
Range("C1").Resize(.Count, 2) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[silverlucky5]

Hi Mick, Well, considering that "beggars can't be choosers", I almost hate to say this, but it is almost perfect! The only thing is that the resulting list in cols C and D seem to drop the last number, i.e., 85670 which exists in both the original Col A and B. Thanks so much for the time you spent on this!