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

 

[Simon Letten]

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.

 

[Simon Letten]

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.

 

[vect98]

Dim MyValue As Variant
Dim FromSheet As Worksheet
Dim LookupColumn As Integer
Dim FromRow As Long
Dim FromColumn As Integer
'-
Dim ToSheet As Worksheet
Dim StartRow As Long
Dim LastRow As Long
Dim ActiveColumn As Integer
Dim ReturnColumnNumber
Dim ToRow As Long
Dim FoundCell As Object

'=============================================================
'- MAIN ROUTINE
'=============================================================
Sub DO_LOOKUP()
Application.Calculation = xlCalculationManual
'----------------------------------------------------------
'- LOOKUP SHEET [**AMEND AS REQUIRED**]
Set FromSheet = Workbooks("Book1.xls").Worksheets("MD")
LookupColumn = 2 ' look for match here
FromColumn = 2 ' return value from here
'-----------------------------------------------------------
'- ACTIVE SHEET
Set ToSheet = ActiveSheet
ActiveColumn = ActiveCell.Column
StartRow = ActiveCell.Row
'-------------------------------------------------------------
'- COMMENT OUT UNWANTED LINE, UNCOMMENT THE OTHER
'- ..............................[** FOR MULTIPLE ROWS **]
LastRow = ToSheet.Cells(65536, ActiveColumn).End(xlUp).Row
'-
'- ..............................[** FOR A SINGLE VALUE **]
' LastRow = ActiveCell.Row
'-------------------------------------------------------------
'- COLUMN NUMBER TO PUT RETURNED VALUE [**AMEND AS REQUIRED**]
ReturnColumnNumber = 2 ' column number
'-------------------------------------------------------------
'- loop through each row (which may be only 1)
For ToRow = StartRow To LastRow
MyValue = ToSheet.Cells(ToRow, ActiveColumn).Value
FindValue
Next
'-------------------------------------------------------------
'- finish
MsgBox ("Done")
Application.Calculation = xlCalculationAutomatic
End Sub
'== END OF PROCEDURE
====================================================

'========================================================================
'- FIND VALUE
'========================================================================
Private Sub FindValue()
' Dim VendMat As String
' Dim matDesc As String
' Dim startDate As String
' Dim BUN As String
Set FoundCell = _
FromSheet.Columns(LookupColumn).Find(MyValue,
LookIn:=xlValues)
If FoundCell Is Nothing Then
MsgBox ("Material No. " & MyValue & " not found in Master
List.")
'Paste this value to MD
'-----
' VendMat = Sheets("Sheet1").Select
' VendMat = Range("C65536").End(xlUp).Offset(0, 0).Select
'VendMat = Selection.Copy
'------

Sheets("MD").Select
Range("B:B").Select

Range("B65536").End(xlUp).Offset(1, 0).Select
IsEmpty (ActiveCell)
ActiveCell = MyValue

' Sheets("MD").Select
' Range("C:C").Select
' Range("C65536").End(xlUp).Offset(1, 0).Select
' IsEmpty (ActiveCell)
' ActiveCell.Select = VendMat
' ActiveCell = VendMat

'---------------------------------------------

Else
FromRow = FoundCell.Row
'- transfer additional data.
ToSheet.Cells(ToRow, ReturnColumnNumber).Value = _
FromSheet.Cells(FromRow, FromColumn).Value
End If
End Sub
'

This works fine in detecting and copying the new material number
accross to the master data sheet, but now i want it to copy the row in
which the new material number is located as there is other information
that goes with so it doesn't have to be manually typed in.

TIA

 

[vect98]

Bump*Bump*