Macros to compare values in two columns and place the duplicates in third column .......

[olutoyin]

I need macro script that will compare values in column a with values in column b,if there are values that are in a but not in b, such values are copied into column c, if there are values in column b that are not in column a, such values are copied to column d and place the duplicates (values in column a that also appear in column b) are copied into column e.

The Code should run from row 2 on all the 5 columns to give allowance for the column titles which will be in the 1st row of each column.

I hope i can get macros that can be used for this. Please any prompt response will be appreciated.

Thanks

 

[Comfy]

In that case. Try this:

Sub Dupes()
    Dim i As Long
        For i = 2 To ActiveSheet.UsedRange.Rows.Count + 1
            If Cells(i, 2).Value = "" And Cells(i, 1).Value <> Cells(i, 2).Value Then
                Cells(i, 3).Value = Cells(i, 1).Value
            ElseIf Cells(i, 1).Value = "" And Cells(i, 1).Value <> Cells(i, 2).Value Then
                Cells(i, 4).Value = Cells(i, 2).Value
            ElseIf Cells(i, 1).Value <> "" And Cells(i, 2).Value <> "" And Cells(i, 1).Value = Cells(i, 2).Value Then
                Cells(i, 5).Value = Cells(i, 1).Value
            End If
        Next
End Sub

 

[olutoyin]

There is a reason why i want it as i stated in my earlier posts.

Column A is going to be titled January while column b will be titled February.

I want to know values in January which is not in February as values that have not been retained.

Values in February not in January are newly acquired

while values in January which are also in February are retained

this is why these conditions

(Values in column a but not in column b is placed in column c
values in column b but not in column a is placed in column d) are not the same.

 

[mirabeau]

Another code

Sub twocols()
Dim d As Object, na&, nb&, a, b
Dim e, p&, q&, r&, m
Set d = CreateObject("scripting.dictionary")
na = Range("A" & Rows.Count).End(3).Row
a = Range("A2:A" & na)
nb = Range("B" & Rows.Count).End(3).Row
b = Range("B2:B" & nb)
ReDim c(1 To Application.Max(na, nb), 1 To 3)
For Each e In a: d(e) = 1: Next
For Each e In b
    If d(e) = 1 Then
        r = r + 1
        c(r, 3) = e
    Else
        q = q + 1
        c(q, 2) = e
End If
Next
d.RemoveAll
For Each e In b: d(e) = 1: Next
For Each e In a
    If d(e) <> 1 Then
        p = p + 1
        c(p, 1) = e
    End If
Next
m = Application.Max(p, q, r)
Range("C2").Resize(m, 3) = c
End Sub

 

[Comfy]

There is a reason why i want it as i stated in my earlier posts.

Column A is going to be titled January while column b will be titled February.

I want to know values in January which is not in February as values that have not been retained.

Values in February not in January are newly acquired

while values in January which are also in February are retained

this is why these conditions

(Values in column a but not in column b is placed in column c
values in column b but not in column a is placed in column d) are not the same.

Thanks, that makes more sense. I see that it's not just a row by row comparison.

 

[MickG]

Try :-

[COLOR="Navy"]Sub[/COLOR] MG22Jun19
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K
[COLOR="Navy"]Dim[/COLOR] max [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
    
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & rows.Count).End(xlUp))
     [COLOR="Navy"]Set[/COLOR] nRng = Range(Range("B2"), Range("B" & rows.Count).End(xlUp))
        max = Application.max(Rng.Count, nRng.Count)
            [COLOR="Navy"]Set[/COLOR] Rng = Range("A2").Resize(max)
                ReDim Ray(1 To max * 2)
            [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
                .CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] Ac = 1 To 2
        [COLOR="Navy"]If[/COLOR] Ac = 2 [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] Rng = Rng.Offset(, 1)
            [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"]If[/COLOR] Not .Exists(Dn.value) [COLOR="Navy"]Then[/COLOR]
                    .Add Dn.value, Ac
                [COLOR="Navy"]Else[/COLOR]
                    n = n + 1
                    Ray(n) = Dn.value
                    .Remove Dn.value
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]Next[/COLOR] Ac
Range("e2").Resize(n) = Application.Transpose(Ray)
c = 1: p = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    [COLOR="Navy"]If[/COLOR] .Item(K) = 1 [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        Cells(c, 3) = K
    [COLOR="Navy"]Else[/COLOR]
        p = p + 1
        Cells(p, 4) = K
     [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[olutoyin]

Thanks a lot. It worked well. I'm grateful

 

[olutoyin]

Thank you all comfy, mick and mirabeau. the codes worked well.
i am so happy i joined this forum

 

[myexcel67]

Dear Comfy,
I hope the author wants like this
for example column A contains 1 2 3 4 5 6 7 8 9
column B contains 1 3 5 7 9 11 13
then
Column C should return 2 4 6 8
Column D should return 11 13
Column E should return 1 3 5 7 9
I hope you understand the question.