compare two different worksheets using dictionary

[reggieneo]

Hi All, my head is spinning now on this and I can't figure out how to accurately compare 2 sheets of 2 columns. What I would like to do is, IF ID and WO# in Sheet1 is not found in Sheet2 then the entire row (has 52 columns) that has the new sets of ID and WO# be copied to the next blank row in Sheet2. it needs to resize up to the column AZ . in the table below, the result should be row 5, entire row up to column AZ in Sheet1 must be also in Sheet2. Appreciate if this can be done in Vba. Thanks so much . Sheet1 [TABLE="width: 500"]
<tbody>[TR]
[TD]1
[/TD]
[TD]ID
[/TD]
[TD]Name
[/TD]
[TD]S/N
[/TD]
[TD]WO#
[/TD]
[TD]Description
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]12345
[/TD]
[TD]Harry
[/TD]
[TD]1
[/TD]
[TD]33221
[/TD]
[TD]Repair
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]45678
[/TD]
[TD]Leo
[/TD]
[TD]1
[/TD]
[TD]44532
[/TD]
[TD]Delivery
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]91012
[/TD]
[TD]Bert
[/TD]
[TD]1
[/TD]
[TD]23432
[/TD]
[TD]Paint
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]35555
[/TD]
[TD]Bert
[/TD]
[TD]1
[/TD]
[TD]35555
[/TD]
[TD]Admin Works
[/TD]
[/TR]
</tbody>[/TABLE]
Sheet2 [TABLE="width: 500"]
<tbody>[TR]
[TD]1
[/TD]
[TD]ID
[/TD]
[TD]Name
[/TD]
[TD]S/N
[/TD]
[TD]WO#
[/TD]
[TD]Description
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]12345
[/TD]
[TD]Harry
[/TD]
[TD]1
[/TD]
[TD]33221
[/TD]
[TD]Repair
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]45678
[/TD]
[TD]Leo
[/TD]
[TD]1
[/TD]
[TD]44532
[/TD]
[TD]Delivery
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]91012
[/TD]
[TD]Bert
[/TD]
[TD]1
[/TD]
[TD]23432
[/TD]
[TD]Paint
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[reggieneo]

Hi Peter, the code copied the item (entire rows) that is already present in my Sheet2. And strange that when I F8 through it a dialog box "Update Values: Sheet2" prompted me to open. I click cancel and the rows copied the ones that is already in sheet2. Please Allow me to clarify more: the sheet2 should constantly be receiving new data from sheet1. and it should not copy any more data that is already present .

  Dim ws1 As Worksheet, ws2 As Worksheet
  Dim nr As Long
  
  
  Set ws1 = Workbooks("MDB.xlsx").Sheets("MDB"): Set ws2 = ThisWorkbook.Sheets("DWS1")
  nr = ws2.Range("B" & ws2.rows.Count).End(xlUp).row + 1
  With ws1
    .Range("BZ7").Formula = Replace(Replace("=COUNTIFS('#'!B$7:B$%,B8,'#'!E$7:E$%,E8)=0", "#", ws2.Name), "%", nr - 1)
    .Range("B7", .Range("B" & .rows.Count).End(xlUp)).Resize(, 52).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("BZ7:BZ8"), CopyToRange:=ws2.Cells(nr, 2), Unique:=False
    .Range("BZ7").ClearContents
  End With
  ws2.rows(nr).Delete

 

[Peter_SSs]

There are a few issues at work here.

the sheet2 should constantly be receiving new data from sheet1.

1. Do you mean dynamically? That is if new data appears in Sheet1 it automatically appears in sheet2 without having to, say, run a macro like this? Or are you happy to just have new data transferred when the macro is run?

2. There was no mention previously that the two sheets were in different workbooks. That is why you were prompted to open something but in any case my code so far was written to work on two sheets in the same workbook, not different workbooks. That will cause my code to fail as it is currently written.

3. Even if the above point was not a problem, you didn't adapt the CriteriaRange from my code correctly. The formula should be in the lower cell of the 2-cell criteria range, not the upper cell as you have it written. So with the formula in BZ7 the CriteriaRange should have been BZ6:BZ7.

4. In ws1, what row are the headings in?

5. In ws2, what row are the headings in?

6. I'm still interested in this:

(Interested to know about how many rows of data you do have as the best/fastest method may be different)

Having said all that, if we are dealing with 2 different workbooks, my approach will probably be more cumbersome & prone to difficulties that the sorts of methods others have suggested in the thread. Did you try any of those?

 

[reggieneo]

Hi Peter, thanks for time in replying this. I have sorted the issue by copying the sheet 1 and sheet 2 together in another sheet then remove duplicates, all in macro. it is also a bit cumbersome but I think I could live with this since its all automated anyway. always appreciate the effort and help.