VBA for compare two columns and show differences in another

chuf · Jul 16, 2008

Hello.

I've spent the last two hours searching for this, i know its here.. but i can't find it.

I saw a great code which compared the values in two columns A and B, data such as A123 and then shows, in another column ie C , things in A and not in B and in another column ie D things in B and not in A.

Really appreciate your help in doing this, have barmy colleague who i've promised to help

Ta in advance. Chuf

chuf · Jul 16, 2008

in a last ditch attempt, i think i may have answered my own question. Its a jonmo1 code...

Sub Test()
Dim ListA As Range
Dim ListB As Range
Dim c As Range
Set ListA = Range("A1:A100")
Set ListB = Range("B1:B100")
Range("C1").Value = "Values in A that are NOT in B"
Range("D1").Value = "Values in B that are Not in A"
Range("E1").Value = "Count of A"
Range("F1").Value = "Count of B"
For Each c In ListA
If c.Value <> "" Then
Range("E2").Value = Range("E2").Value + 1
If Application.CountIf(ListB, c) = 0 Then
Cells(Cells(Rows.Count, "C").End(xlUp).Row + 1, "C").Value = c
End If
End If
Next c
For Each c In ListB
If c.Value <> "" Then
Range("F2").Value = Range("F2").Value + 1
If Application.CountIf(ListA, c) = 0 Then
Cells(Cells(Rows.Count, "D").End(xlUp).Row + 1, "D").Value = c
End If
End If
Next c
End Sub

How would i make the range dynamic, and would this code only show the differences once ? for example in column A and B the codes may be shown more than once... would column C and/or D therefore show the differences more than once.

I'm new to this code so am trying to learn the logic.

Thanks Chuf

jindon · Jul 16, 2008

try

Code:

Sub test()
Dim a, i As Long, b(), n As Long
a = Range("a1").CurrentRegion.Resize(,2).Value
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 1 To UBound(a,1)
        If (Not IsEmpty(a(i,1))) * (Not .exists(a(i,1))) Then .add a(i,1), Nothing
    Next
    ReDim b(1 To UBound(a,1), 1 To 1)
    For i = 1 To UBound(a,1)
        If Not IsEmpty(a(i,2)) Then
            If  Not .exists(a(i,2)) Then
                n = n + 1 : b(n,1) = a(i,2)
            Else
                .remove a(i,2)
            End If
        End If
    Next
    With Range("d1")
        .CurrentRetion.ClearContents
        .Resize(,2).Value = [{"Not in A", No in B"}]
        With .Offset(1)
            If n > 0 Then .Resize(n) .Value = b
        End With
        With .Offset(1,1)
            If .Count > 0 Then .Resize(.Count).Value = Application.Transpose(.keys)
        End With
    End with
End With
End Sub

chuf · Jul 16, 2008

Hi Jindon,

i get object doesn't support this type or property of method ?!

jindon · Jul 16, 2008

typo

Rich (BB code):

        .CurrentRetion.ClearContents

should be

Rich (BB code):

        .CurrentRegion.ClearContents

chuf · Jul 16, 2008

Column D shows the values in B and not in A - great.

Column E is empty though

The headers in column D and E show #Value!

any ideas ?

jindon · Jul 16, 2008

chuf said:
Column D shows the values in B and not in A - great.
Column E is empty though

Then possiblely no item to be displayed ?

The headers in column D and E show #Value!

Missed " (double quotes)

Rich (BB code):

        .Resize(,2).Value = [{"Not in A", No in B"}]

should be

Rich (BB code):

        .Resize(,2).Value = [{"Not in A", "No in B"}]

chuf · Jul 16, 2008

Thanks for taking time to discuss Jindon.

There are items to be displayed.

Thanks for the headings, i should noticed that myself but its all Greek to me !

I'm heading off to the office now.. but will carry on my quest - this will make such a difference to one of the teams.. the poor woman spends ages looking at all the lines to check for differences

This stuff is brilliant.

jindon · Jul 16, 2008

OOps
try

Code:

Sub test()
Dim a, i As Long, b(), n As Long, x
a = Range("a1").CurrentRegion.Resize(,2).Value
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 1 To UBound(a,1)
        If (Not IsEmpty(a(i,1))) * (Not .exists(a(i,1))) Then .add a(i,1), Nothing
    Next
    ReDim b(1 To UBound(a,1), 1 To 1)
    For i = 1 To UBound(a,1)
        If Not IsEmpty(a(i,2)) Then
            If  Not .exists(a(i,2)) Then
                n = n + 1 : b(n,1) = a(i,2)
            Else
                .remove a(i,2)
            End If
        End If
    Next
    x = .keys
End With
With Range("d1")
    .CurrentRetion.ClearContents
    .Resize(,2).Value = [{"Not in A", "No in B"}]
    With .Offset(1)
        If n > 0 Then .Resize(n) .Value = b
    End With
    On Error Resume Next
    .Offset(1,1).Resize(.Count).Value = Application.Transpose(x)
End With
End Sub

chuf · Jul 16, 2008

jindon, you're a star.

thanks very much. ( had to tweak the retion to region - in case anyone copies and pastes this fab code themselves)

i will try and adapt to looking at different worksheets etc... have a go myself and if i fail i hope the board will be able to show me the way.

I will have one happy colleague soon. Chuf

VBA for compare two columns and show differences in another

chuf

Well-known Member

Excel Facts

chuf

Well-known Member

jindon

MrExcel MVP

chuf

Well-known Member

jindon

MrExcel MVP

chuf

Well-known Member

jindon

MrExcel MVP

chuf

Well-known Member

jindon

MrExcel MVP

chuf

Well-known Member

Similar threads

Forum statistics

Share this page

VBA for compare two columns and show differences in another

Well-known Member

Excel Facts

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock