Extract only non-matching data
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 -- vect98 ------------------------------------------------------------------------ vect98's Profile: http://www.excelforum.com/member.php...o&userid=26365 View this thread: http://www.excelforum.com/showthread...hreadid=396372 |
Extract only non-matching data
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").Rang e("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 -- HTH Simon "vect98" wrote: 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 -- vect98 ------------------------------------------------------------------------ vect98's Profile: http://www.excelforum.com/member.php...o&userid=26365 View this thread: http://www.excelforum.com/showthread...hreadid=396372 |
Extract only non-matching data
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. :) -- vect98 ------------------------------------------------------------------------ vect98's Profile: http://www.excelforum.com/member.php...o&userid=26365 View this thread: http://www.excelforum.com/showthread...hreadid=396372 |
Extract only non-matching data
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. -- Simon "vect98" wrote: 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. :) -- vect98 ------------------------------------------------------------------------ vect98's Profile: http://www.excelforum.com/member.php...o&userid=26365 View this thread: http://www.excelforum.com/showthread...hreadid=396372 |
Extract only non-matching data
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 ------------------------------------------------------------------------ vect98's Profile: http://www.excelforum.com/member.php...o&userid=26365 View this thread: http://www.excelforum.com/showthread...hreadid=396372 |
Extract only non-matching data
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 ------------------------------------------------------------------------ vect98's Profile: http://www.excelforum.com/member.php...o&userid=26365 View this thread: http://www.excelforum.com/showthread...hreadid=396372 |
Extract only non-matching data
Bump*Bump* -- vect98 ------------------------------------------------------------------------ vect98's Profile: http://www.excelforum.com/member.php...o&userid=26365 View this thread: http://www.excelforum.com/showthread...hreadid=396372 |
All times are GMT +1. The time now is 02:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com