VBA for compare two columns and show differences in another

[chuf]

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

 

[egibberate]

I tried this but i get a syntax error? Anyone point out why?

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]

I took the extra space out in between and the .Value and it worked.

all the best. Chuf

 

[egibberate]

Thanks Chuf, much obliged

 

[chuf]

hey.. that's the first time i've been able to help anyone on here..
you've made my day.
best regards.

 

[Pretty1996]

I wonder if anyone can still help me in here.

I'm using the code shown in this thread (thank you for supplying it)

however for some weird reason I cant get it work properly.

what I mean is it shows all values that are missing in column A but it doesnt show all values missing in column B?

any help or suggestions?

thanks

 

[abmanish71]

Can you attached the excel sheet
and request you to send the mail to me

Thanking You

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

 

[abmanish71]

Can you attached the excel sheet
and request you to send the mail to me

Thanking You

try

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

 

[Pretty1996]

Hi abmanish, do you need help with this or do you want help with the solution?

 

[montecarlo2012]

I found this post by real miracle, I am looking almost the same, the difference is instead of two columns, I am trying with two arrays, it is possible?.

 

[Pretty1996]

Try this,

reply number 5 by GTO.

http://www.mrexcel.com/forum/showthread.php?t=494348