Look for new entries in data

[Steve]

Hi everyone. Given a block of data in Sheet1 column A. Can I have
vba cross reference that data against data in Sheet2 column A and make
a list (on sheet3) of ALL entries that exist on Sheet2 but NOT on
Sheet1? Thanks!

 

[JW]

Here's one way. This could be done without all of the variables, but
it just makes modifications a little easier. Keep in mind, this does
not remove anything from sheet3 before placing in the new data. It
simply identifies the first available cells in column A and places the
data there. It also ony does column A, not the entire row. That is a
simple change if that is what you want though.

Sub newEntry()
Dim searchRange As Range, r As Range
Dim searchSht As Worksheet
Dim valSheet As Worksheet
Dim placementSheet As Worksheet
Dim valRange As Range
Set searchSht = Sheets("Sheet1")
Set valSheet = Sheets("Sheet2")
Set placementSheet = Sheets("Sheet3")
Set valRange = valSheet.Range _
(valSheet.Cells(2, 1), _
valSheet.Cells(valSheet.Rows.Count, 1) _
.End(xlUp))
Set searchRange = searchSht.Range _
(searchSht.Cells(2, 1), _
searchSht.Cells(searchSht.Rows.Count, 1) _
.End(xlUp))
For Each r In valRange
If WorksheetFunction.CountIf(searchRange, _
r.Text) = 0 Then
placementSheet.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Value = r.Text
End If
Next r
Set searchSht = Nothing
Set valSheet = Nothing
Set placementSheet = Nothing
Set valRange = Nothing
Set searchRange = Nothing
End Sub

 

[Steve]

Hi JW. Thanks so much! Worked great!! But it brought back SO many
entries!!

So, based on that, can we only populate the list with entries where
the value in Column AA <> zero? So I guess conceptully the code would
first test the value based on cell AA, and if > zero, then test to see
if it exists in the other list? No sense in me bringing back entries
with no value. Thank you!!

One other thing I need to do is take the created list and reduce it to
uinique values, which I guess can be easily done after we get the
original list

 

[JW]

Hi JW. Thanks so much! Worked great!! But it brought back SO many
entries!!

So, based on that, can we only populate the list with entries where
the value in Column AA <> zero? So I guess conceptully the code would
first test the value based on cell AA, and if > zero, then test to see
if it exists in the other list? No sense in me bringing back entries
with no value. Thank you!!

One other thing I need to do is take the created list and reduce it to
uinique values, which I guess can be easily done after we get the
original list

Do you want to clear the existing values in Sheet3 first? Or do you
just want to append the values the existing data already in Sheet3?
Getting the unique values shouldn't be too difficult using a
Collection. Also, you only want to process the data is column AA is
anything other than 0 or if AA is greater than 0? Just want to get
all of my ducks in a row before the rewrite.

Regards
-Jeff-

 

[Steve]

Hi Jeff. Thanks for the response. To asnwer your questions:
-clearing Sheet3 would be ideal
-Only if AA > zero.

Thanks for your help!!

 

[JW]

Hi Steve. Give this a shot.

Sub newEntry()
Dim searchRange As Range, r As Range
Dim searchSht As Worksheet
Dim valSheet As Worksheet
Dim placementSheet As Worksheet
Dim valRange As Range
Dim uVal As Collection, u As Variant
Set searchSht = Sheets("Sheet1")
Set valSheet = Sheets("Sheet2")
Set placementSheet = Sheets("Sheet3")
Set valRange = valSheet.Range _
(valSheet.Cells(2, 1), _
valSheet.Cells(valSheet.Rows.Count, 1) _
.End(xlUp))
Set searchRange = searchSht.Range _
(searchSht.Cells(2, 1), _
searchSht.Cells(searchSht.Rows.Count, 1) _
.End(xlUp))
Set uVal = New Collection
For Each r In valRange
If valSheet.Cells(r.Row, 27) > 0 And _
WorksheetFunction.CountIf(searchRange, _
r.Text) = 0 Then
On Error Resume Next
uVal.Add r.Text, CStr(r.Text)
On Error GoTo 0
End If
Next r
With placementSheet
.Cells.ClearContents
.Cells(1, 1).Value = "Unique New"
For Each u In uVal
.Cells(.Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Value = u
Next u
End With
Set searchSht = Nothing
Set valSheet = Nothing
Set placementSheet = Nothing
Set valRange = Nothing
Set searchRange = Nothing
Set uVal = Nothing
End Sub