Excel VBA for finding unique values and updating column

[vbanoob35]

Hi!

I know there have been a lot of threads for finding the unique values using macro but my problem is unique in its own way.

I need to compare 2 columns from two sheets of the same workbook and find the unique value. The data that I need is from Sheet 2 column C. Sheet1 Column B has a lot of #N/A and zeros because these were v-looked up using another column (but that's another story)

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]Sheet1[/TD]
[TD]Sheet2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Melon[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Pear[/TD]
[/TR]
[TR]
[TD]Grape[/TD]
[TD]Guava[/TD]
[/TR]
[TR]
[TD]Lime[/TD]
[TD]Grape[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD]Lime[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]Kiwi[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]Melon[/TD]
[/TR]
[TR]
[TD]Guava[/TD]
[TD]Lemon[/TD]
[/TR]
[TR]
[TD]Melon[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]






















I need to identify the unique values in column C Sheet 2 in comparison to column B Sheet 1. All unique values from Sheet 2 column C, will then be added to the lastrow+1 of Sheet 1 column B. so it should look like this:

[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]Sheet 1[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Grape[/TD]
[/TR]
[TR]
[TD]Lime[/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]Guava[/TD]
[/TR]
[TR]
[TD]Melon[/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Kiwi[/TD]
[/TR]
[TR]
[TD]Lemon[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Both columns have dynamic ranges. The problem with my formulas is that column B sheet 1 has a lot of 0's and #N/A's and the row number is different from Column c sheet 2, so formulas doesn't really work as it cannot find the real unique values.

I would really appreciate it if anyone could help me I'm very new to VBA, and I am just halfway through with my macro. this problem is holding me backs for weeks now.

Thanks!

 

[Fishboy]

Hi vbanoob35, welcome to the boards.

Try out the following in a COPY of your workbook:

Sub CompleteList()
' Defines variables
Dim Cell As Range, cRange As Range, sRange As Range, LastRow As Long, LastRow2 As Long


' Defines LastRow as the last row of data on Sheet2 based on column C
LastRow = Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Row
' Defines LastRow2 as the first blank row of data on Sheet1 based on column B
LastRow2 = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row + 1


' Sets the check range as C1 to the the last row of C on Sheet2
Set cRange = Sheets("Sheet2").Range("C1:C" & LastRow)
' Sets the search range as B1 to the last row of B on Sheet1
Set sRange = Sheets("Sheet1").Range("B1:B" & LastRow2)


' For each cell in the check range
For Each Cell In cRange
    ' If the cell value does not exist in the search range on Sheet1 then...
    If Application.WorksheetFunction.CountIf(sRange, Cell.Value) = 0 Then
        ' Copy the cell value to the next blank row on Sheet1
        Cell.Copy Sheets("Sheet1").Range("B" & LastRow2)
        ' Increase LastRow2 by 1 to account for the new data
        LastRow2 = LastRow2 + 1
        ' Increases the search range on Sheet1 to help prevent duplicates
        Set sRange = Sheets("Sheet1").Range("B1:B" & LastRow2)
    End If
' Check next cell
Next Cell


End Sub

 

[vbanoob35]

Oh my God Fishboy this is amazing!

you did it! it works like a charm!

A big thank you to you!

 

[Fishboy]

Oh my God Fishboy this is amazing!

you did it! it works like a charm!

A big thank you to you!

Happy to help

 

[deckerp]

this Looks as a starting point for what I need.

Is there a way to look at two different sheet columns?
I need some code that retrieves unique values from two other sheets (same workbook) and copies no duplicates.
Any idea?