VBA Search two columns and pick out unique values

[behedwin]

Hi
I want to create a worksheet where i have one button and 3 columns.

Column A = data that i paste in
Column B = data that i paste in
Column C = data with result

Buttun "run"

When user clicks on button. I want a VBA code to pick out all unique values and put them in Column C.
The idea is to compare the two columns (A and B) to each other so i can find what values are unique.
Each value in column A will be unique to it´s own column
Same with column B.

So i only want to find the values that are unique by comparing each cell to the other column.

How can i do this with VBA so i can run the command when pressing a button?

 

[Kamolga]

Sub BtnUniqueValue()
Dim Rnga As Range
Dim Rngb As Range
Dim Rngc As Range
Dim aCell As Range
Dim bCell As Range
Dim cCell As Range
Dim Col As New Collection
Dim item
Dim i As Long
[COLOR=#008000]'Set up ranges until last rows[/COLOR]
Set Rnga = ActiveSheet.Range("A1:A" & ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row)
Set Rngb = ActiveSheet.Range("b1:b" & ActiveSheet.Cells(ActiveSheet.Rows.Count, "b").End(xlUp).Row)
Set Rngc = ActiveSheet.Range("c1:c" & ActiveSheet.Cells(ActiveSheet.Rows.Count, "c").End(xlUp).Row)
[COLOR=#008000]'Clear previous list in column C[/COLOR]
 Rngc.ClearContents
[COLOR=#008000]'Check if cellA is in Range B[/COLOR]
For Each aCell In Rnga
    Set bCell = Rngb.Find(aCell.Value)
    [COLOR=#008000]'If not add it to collection[/COLOR]
    If bCell Is Nothing Then
        Col.Add aCell
    End If
Next aCell
[COLOR=#008000]'Check if cell b is in Range A[/COLOR]
For Each bCell In Rngb
    Set aCell = Rnga.Find(bCell.Value)
   [COLOR=#008000] 'If not add it to collection[/COLOR]
    If aCell Is Nothing Then
        Col.Add bCell
    End If
Next bCell
[COLOR=#008000]'Print collection[/COLOR]
i = 1
For Each item In Col
    Cells(i, "C").Value = item
    i = i + 1
Next item
End Sub

 

[behedwin]

Thank you.
First issue i came upon is that i now in my design want the list to start at row 4.
I changed the code

Set Rnga = ActiveSheet.Range("A1:A" & ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row)
Set Rngb = ActiveSheet.Range("b1:b" & ActiveSheet.Cells(ActiveSheet.Rows.Count, "b").End(xlUp).Row)
Set Rngc = ActiveSheet.Range("c1:c" & ActiveSheet.Cells(ActiveSheet.Rows.Count, "c").End(xlUp).Row)

But the C column still starts to print at row 1 and not row 4

 

[Kamolga]

But the C column still starts to print at row 1 and not row 4

[FONT=Consolas][COLOR=#008000]
'Print collection[/COLOR][/FONT][COLOR=#333333][FONT=monospace]
[/FONT][/COLOR][LEFT][COLOR=#333333][FONT=monospace]i = [/FONT][/COLOR][COLOR=#ff0000]4[/COLOR][COLOR=#333333][FONT=monospace]
[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]For Each item In Col
    Cells(i, "C").Value = item
[/FONT][/COLOR][/LEFT]

 

[behedwin]

[FONT=Consolas][COLOR=#008000]
'Print collection[/COLOR][/FONT][COLOR=#333333][FONT=monospace]
[/FONT][/COLOR][LEFT][COLOR=#333333][FONT=monospace]i = [/FONT][/COLOR][COLOR=#ff0000]4[/COLOR][COLOR=#333333][FONT=monospace]
[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]For Each item In Col
    Cells(i, "C").Value = item
[/FONT][/COLOR][/LEFT]

Aha thanks.
But how come when i type i = 4
Cell 3 is cleared where i have a heading on that row?

 

[Kamolga]

[LEFT][COLOR=#333333][FONT=monospace]Set Rngc = ActiveSheet.Range("c[/FONT][/COLOR][COLOR=#ff0000]4[/COLOR][COLOR=#333333][FONT=monospace]:c" & ActiveSheet.Cells(ActiveSheet.Rows.Count, "c").End(xlUp).Row)[/FONT][/COLOR][/LEFT]
[FONT=Consolas][COLOR=#008000]'Clear previous list in column C
[/COLOR][/FONT][COLOR=#ff0000]If [/COLOR][COLOR=#ff0000]ActiveSheet.Cells(ActiveSheet.Rows.Count, "c").End(xlUp).Row>=4 then[/COLOR]
[LEFT] Rngc.ClearContents
[LEFT][COLOR=#ff0000][FONT=Tahoma]End if[/FONT][/COLOR][/LEFT]
<strike></strike>
[/LEFT]

If your last row in C was 3, it would clear

$C$4:$C3
so now last row need to be >= 4
$C$4:$C456
to clear the existing list​

 

[behedwin]

Thank you, this seems to have fixed it

 

[Kamolga]

You are welcome, thanks for the feedback