Delete Duplicate Rows from Two Worksheets

[wimalik]

Urgent Help: I get a daily log of data for which I want to run a VBA code to compare data in sheet 1 and sheet 2 and then copy common rows to another sheet, and unique rows to another sheet. Sheet 1 has the correct data, and sheet 2, is where I would like to look and find unique and duplicate rows.

Issue #1: I am unable to successfully do this for 100,000 plus rows as excel crashes.

Issue #2: I am unsure how to create a sheet for unique rows where there is no match.

Issue #3: Does no always copy duplicates.

Could someone help me make changes to this code to help meet it's functionality.

Sub Duplicate_Rows()
 Dim ur1 As Range, ur2 As Range, dupeRows As Range
    Dim r1 As Range, s1 As String, r2 As Range, s2 As String

    Set ur1 = Worksheets("qry").UsedRange.Rows
    Set ur2 = Worksheets("Dump").UsedRange.Rows  'Find duplicates from Sheet1 in Sheet2

    Set dupeRows = ur2(Worksheets("Dump").UsedRange.Rows.Count + 1)
    For Each r1 In ur1
        s1 = Join(Application.Transpose(Application.Transpose(r1)))
        For Each r2 In ur2
            s2 = Join(Application.Transpose(Application.Transpose(r2)))
            If s1 = s2 Then
                If Intersect(dupeRows, r2) Is Nothing Then
                    Set dupeRows = Union(dupeRows, r2)
                End If
            End If
        Next
    Next

    Dim wb As Workbook, wsDupes As Worksheet    'Move duplicate rows to new Sheet
    Application.ScreenUpdating = False
    Set wb = ThisWorkbook
    Set wsDupes = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
    wsDupes.Name = "Dump Duplicates - " & Format(Now, "yyyymmdd-hhmmss")
    dupeRows.Copy
    With wsDupes.Cells(1)
        .PasteSpecial xlPasteAll
        .PasteSpecial xlPasteColumnWidths
        .Select
    End With
    dupeRows.EntireRow.Delete
    Application.ScreenUpdating = True
End Sub

 

[alansidman]

Please supply some sample data using XL2BB showing before and after scenarios.

 

[wimalik]

 

[wimalik]

(Sheet 1)
EMG	eNodeBName	ProductNumber	ProductName	SerialNumber	PA_Rating	ID	ID_NAME
AF12	AF_YUP	Apple	Iphone	TD3R037		12​	AF12X4
AE13	AE_TINY	HP	Laptop	TD3R06		1​	AE13X6
Sheet 2
EMG	eNodeBName	ProductNumber	ProductName	SerialNumber	PA_Rating	ID	ID_NAME
AF12	AF_NO	Apple	Iphone	TD3R037		12​	AF12X4
AE13	AE_TINY	HP	Laptop	TD3R06		1​	AE13X6

 

[wimalik]

I have attached sample data. The After isn't doing anything for this

 

[alansidman]

Using Power Query/Get and Transform
1. On the Data Tab, select From Table/Range for each table.
2. With each table in the PQ editor, merge the fields with a delimeter of "|"
3. Join the tables with a left inner join
4. Split the merged table with the same delimeter.
5. Close and Load.

Here are the three Mcodes for the above.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EMG", type text}, {"eNodeBName", type text}, {"ProductNumber", type text}, {"ProductName", type text}, {"SerialNumber", type text}, {"PA_Rating", type any}, {"ID", type text}, {"ID_NAME", type text}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"PA_Rating", type text}}, "en-US"),{"EMG", "eNodeBName", "ProductNumber", "ProductName", "SerialNumber", "PA_Rating", "ID", "ID_NAME"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EMG", type text}, {"eNodeBName", type text}, {"ProductNumber", type text}, {"ProductName", type text}, {"SerialNumber", type text}, {"PA_Rating", type any}, {"ID", type text}, {"ID_NAME", type text}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"PA_Rating", type text}}, "en-US"),{"EMG", "eNodeBName", "ProductNumber", "ProductName", "SerialNumber", "PA_Rating", "ID", "ID_NAME"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

End result

Book12
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Merged.1	Merged.2	Merged.3	Merged.4	Merged.5	Merged.6	Merged.7	Merged.8	Table3.Merged.1	Table3.Merged.2	Table3.Merged.3	Table3.Merged.4	Table3.Merged.5	Table3.Merged.6	Table3.Merged.7	Table3.Merged.8
2	AE13	AE_TINY	HP	Laptop	TD3R06		1	AE13X6	AE13	AE_TINY	HP	Laptop	TD3R06		1	AE13X6
3	AF12	AF_YUP	Apple	Iphone	TD3R037		12	AF12X4
Sheet4