Duplicates in multiple worksheets with an exception

[blacklabel]

Hello everyone,

I'm new to the board, however, I searched the forum before posting my issue. I have one workbook with 2 sheets (source and reference). Here's what I'm trying to do. If any data is found in Column A, source worksheet that is the same as in the reference sheet, Column A, then remove the entire row in the source worksheet.

I hope that makes sense. Your help is greatly appreciated.

[lucas]

Here is one method. It actually checks the entire row for dups. Example attached:
[VBA]
Option Explicit
Sub FindDuplicates()

Dim x As Long
Dim y As Integer
Dim LastRow As Long
Dim c As Object
Dim FirstAddress As String
Dim String1 As String
Dim String2 As String

LastRow = Sheet1.Range("A65536").End(xlUp).Row
For x = 2 To LastRow
String1 = ""
For y = 1 To 256
String1 = String1 & Sheet1.Cells(x, y).Text
Next y
With Sheet2.Range("A:A")
Set c = .Find(what:=Sheet1.Range("A" & x).Text, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
String2 = ""
For y = 1 To 256
String2 = String2 & Sheet2.Cells(c.Row, y).Text
Next y
If String1 = String2 Then
Sheet1.Range("A" & x).EntireRow.Delete
' Exit Do
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
Next x
End Sub
[/VBA]

Its also cap sensitive as is and looks for xlwhole....not just part.

[blacklabel]

Any way to paste what was deleted into a new tab?

Thanks for the reply by the way.

[blacklabel]

The thing to this as well, is I'm comparing the two tabs. I only want to delete rows in the source worksheet that are found in the reference worksheet in Column A.

[mdmackillop]

Revising Steve's example
[VBA]
Option Explicit
Sub FindDuplicates()
Dim x As Long
Dim LastRow As Long
Dim c As Object
Dim Source As Worksheet
Dim Ref As Worksheet
Dim Deleted As Worksheet
Set Source = Sheets("Source")
Set Ref = Sheets("Ref")
Set Deleted = Sheets("Deleted")
LastRow = Source.Cells(Rows.Count, 1).End(xlUp).Row
For x = LastRow To 2 Step -1
With Ref.Range("A:A")
Set c = .Find(what:=Source.Range("A" & x).Text, LookIn:=xlValues, _
lookat:=xlWhole, MatchCase:=True)
If Not c Is Nothing Then
Source.Rows(x).Cut Deleted.Cells(Rows.Count, 1).End(xlUp).Offset(1).EntireRow
End If
End With
Next x
End Sub

[/VBA]

[blacklabel]

Thanks for the reply. I'm working with about 16,000 records. One problem I am having is the format of the reference file. Here's what is in the source file in Column A:

ex. 111-AAA-22222222222222222
And the reference file:
ex. 111-AAA-222222222222222222222

Sometimes the first set of numbers in both files are 2 numbers and sometimes 3 in both files. After the second hyphen, is it possible to remove the only the first 4 zero's in the reference file so it matches the format in the source file?

I hope that made sense.

Thanks in advance.

[lucas]

Here's what is in the source file in Column A:
ex. 111-AAA-22222222222222222
And the reference file:
ex. 111-AAA-222222222222222222222

Sometimes the first set of numbers in both files are 2 numbers and sometimes 3 in both files. After the second hyphen, is it possible to remove the only the first 4 zero's in the reference file so it matches the format in the source file?

I hope that made sense.

Thanks in advance.

Your going to have to clarify this please........

[blacklabel]

In Column A on the source tab, the format is:
ex. 111-AAA-22222222222222222

Sometimes the first set if numbers before the first hyphen (111) can be either two or three numbers (11 or 111) This applies to both sheet1 and sheet2. The 3 letters following the first hyphen will always be 3 letters in both sheets. The problem I am having is in sheet2. As opposed to having 17 numbers after the second hyphen (22222222222222222) as sheet1, there are 21. In sheet2, there are 4 additional leading zeros that I need to have removed automatically to it matches the format of sheet1.

I hope I explained that in a way it makes sense. Let me know if you still need clarification.

[lucas]

Try this which was contributed by John:
Put this in the thisworkbook module:
[VBA]
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ActiveWindow.DisplayZeros = False
End Sub

[/VBA]
It will suppress the leading zero's

[lucas]

also note that your column needs to be formatted as "number" not text

[mdmackillop]

Can you post a SMALL sample on which we can attempt a solution. Use Manage Attachments in the Go Advanced section. If you can provide a sample of your desired output as well, this would help.
Regards
MD

[blacklabel]

Here's a sample:

5-CUC-000000000009111111111
51-SUR-000000000012111111111

Here's what I need done to the two samples:

5-CUC-00000009111111111
51-SUR-00000012111111111

Just 4 of the leading zeros on the long string of numbers removed. Changed from 21 to 17 digits.

[mdmackillop]

Hi Blacklabel,
I'm getting very confused here.
First you ask for some rows to be deleted. Then for the deleted rows to be copied elsewhere. Now for the comparable cells to be edited in some way.
Can you please pull all this together and post a sample workbook. You can add notes there clarifying what is copied to where, what is modified and preserved. I don't believe the solution is complicated, if only I was clear as to what is required.
Regards
MD

[blacklabel]

Ok, let's see if this explains it. Here's the source tab.


And here's the reference tab:


The source tab is compared to the reference tab. If any cells are the same in both tabs, I need the cell removed only from the source tab. I need the deleted cells (that were found in both tabs only) moved to the deleted tab to let me know what cells were removed. That's the best way I can explain it.

The issue with the zeros came up because I noticed (after I made my first post here) that the format of Column A on the source tab had 4 less leading zeros. It wasn't my intention to confuse anyone. I just noticed the code that was posted wasn't working and I thought maybe this issue was the cause.

Anything else you need, just let me know. I hope this helps.