Extract only non-matching data

[vect98]

I've read through similar posts and tried their macros but still can't
find a solution.

I have 2 sheets one which is downloaded each time and a master one.
both have a list of items and i want to be able to with a macro search
the newly downloaded list and only update the master list with items
that a new or don't already exist. So just the non-matching ones. i
tried this macro but i onyl get a list of all data and evcen then im
having trouble copying it accross.

Sub Find_Matches()
Dim CompareRange As Variant
Dim x As Variant
Dim y As Variant
Set CompareRange = Worksheets("Sheet1").Range("C2:C120")
Dim dMat As String
For Each x In Selection
For Each y In CompareRange
If x <> y Then
dMat = y
ActiveCell = dMat
Else
ActiveCell = "No Item"
End If
Next y
Next x

All i want to do is compare 2 columns of the same type - as in use that
as the unique identifier "item number"

Thanks

 

[Guest]

I think it is easier to use the Find method. This code should do the trick:

Sub Add_New_Entries()

Dim rngMasterData As Range
Dim rngNewData As Range
Dim cell As Range
Dim rngEntryFound As Range

Set rngNewData =
Workbooks("newdata.xls").Worksheets("Sheet2").Range("A1:A4") ' <-- change this
Set rngMasterData = ThisWorkbook.Worksheets("Sheet2").Range("C1:C3") '
<-- change this

' loop through all the new data looking for a matching value in master
data
For Each cell In rngNewData
Set rngEntryFound = rngMasterData.Find(What:=cell.Value,
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If (rngEntryFound Is Nothing) Then
' The value in cell.Value does not exist in master data
' So add value to Master data
MsgBox "not found"
Else
' Do nothing
End If
Next cell

CleanUp:
If Not (rngEntryFound Is Nothing) Then Set rngEntryFound = Nothing
If Not (rngNewData Is Nothing) Then Set rngNewData = Nothing
If Not (rngMasterData Is Nothing) Then Set rngMasterData = Nothing

End Sub

 

[vect98]

Hi simon,

thanks for the response.

I tried your macro - modifiying the worksheets where i needed to.

Sub Add_New_Entries()

Dim rngMasterData As Range
Dim rngNewData As Range
Dim cell As Range
Dim rngEntryFound As Range

Set rngNewData =
Workbooks("book4.xls").Worksheets("Sheet1").Range("B2:B900") ' <--
change this
Set rngMasterData = ThisWorkbook.Worksheets("Sheet2").Range("B2:B200")
'
'<-- change this

' loop through all the new data looking for a matching value in master
'data
For Each cell In rngNewData
Set rngEntryFound = rngMasterData.Find(What:=cell.Value,
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If (rngEntryFound Is Nothing) Then
' The value in cell.Value does not exist in master data
' So add value to Master data
MsgBox "not found"
Else

' Do nothing
End If
Next cell

CleanUp:
If Not (rngEntryFound Is Nothing) Then Set rngEntryFound = Nothing
If Not (rngNewData Is Nothing) Then Set rngNewData = Nothing
If Not (rngMasterData Is Nothing) Then Set rngMasterData = Nothing

End Sub


I want to search the long list which is about 900 rows (could get
larger if more things are added) and compare it to the Master List and
if there are new items in the Long list add them to the master list.
WHen i ran the macro it seemed to go through the list but just come up
with "nothing found" even when i added an extra item to the Long list
so something new should've come up. I've tried to figure out how tofix
this but im not sure. your help will be greatly appreacitated.

Thanks heaps.

 

[Guest]

I forgot to add a couple of points to my first post (sorry!)
1. You might need to change the parameters for the Find method, i.e. LookAt,
LookIn and MatchCase depending on how you match your data. This might be the
cuase of the problem.

2. After you add an item to the Master list you'll need to increase the
rngMasterData range, e.g. Set rngMasterData =
rngMasterData.Resize(RowSize:=rngMasterData.Rows+1)

Ok, my reply to your question is, did you change the range to which
rngMasterData refers? i.e. is your Master list on Sheet2 between B2 and
B200. If so, maybe an example of the data would help.

 

[vect98]

The master list (sheet 2) has a list of item no. like the following:
96321
96322
96323
96324
96325
96326
96327
96328
96329
96330
96331
96332
96333
96334
96335
96336
96337
96338
96339

In column B

Sheet 1 (new data that has been downloaded) has the same but also has
an extra one 96340. So i want it to find that 96340 does not exist in
Master Data (sheet 2) and add it. THe master data list is approd 200
rows and contains no duplicates. The Downloaded data contains
duplicates but the items are duplicated the same. I do not wnat to get
rid othe duplicates as i use the other figures qty and dates for
anotehr sheet. The downloaded data list is approx 900 rows long at the
moment it could get a bit longer. Hope this makes senese.