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)

Sheet1	Sheet2
Column B	Column C
Apple	Melon
Orange	Pear
Grape	Guava
Lime	Grape
#N/A	Lime
Pear	Kiwi
0	Melon
Guava	Lemon
Melon	Banana
#N/A

<tbody>
</tbody>

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:

Sheet 1

Column B

Apple

Orange

Grape

Lime

#N/A

Pear

0

Guava

Melon

#N/A

Kiwi

Lemon

Banana

<tbody>
</tbody>

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?