Create new worksheet based of duplicate records

[Qppg]

Hello,
I have a sheet that has alot of part #'s in them. I am in the process of trying to do inventory on our store room. What I am finding is that there are parts in mulitple places in the store room. This process of auditing at least for now is done approx. weekly. So with that I have weekly inventory tabs and a master tab where I have pasted all the weekly data into. What I would like to be able to do is have a button that will generate a new sheet called Duplicate Records copy any rows that have duplicate part #'s from master to the new sheet "Duplicate Records" I appreciate anyones help with this issue. I tried looking around and found alot of information on how to find duplicates and delete them but not how find and isolate to a new sheet.

Example column D contains the serial part numbers.

12/7 B3 FQ 9008188 1
12/13 D1 GA C1 JH 9008373 1
12/7 B5 GA 9010088 1
12/12 C1 GB C1 GD 9011558 6
12/7 B6 BR 9017275 11


Thanks again for your help
Bob

[rylo]

Hi

Add a new sheet to your file called duplicates.

Then try

Sub aaa()
  Dim OutSH As Worksheet
  Set OutSH = Sheets("Duplicates")
  OutSH.Cells.ClearContents
  
  OutSH.Range("1:1").Value = Sheets("Master").Range("1:1").Value
  
  With Sheets("Master")
    For Each ce In .Range("D2:D" & .Cells(Rows.Count, "D").End(xlUp).Row)
      If WorksheetFunction.CountIf(.Range("D:D"), ce.Value) > 1 Then
        ce.EntireRow.Copy Destination:=OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
      End If
    
    Next ce
  
  End With
End Sub

In the sample sheet there was only 1 duplicated entry.


rylo

[Qppg]

rylo,
Perfect.....Thanks so much for your help I really appreciate it.

thanks again,
Bob

[jon1180]

Could this formula be modified to search for duplicate names as opposed to numbers?

Thanks,

[rylo]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.