Find Duplicates and Paste in another sheet??????

[mbarnett]

Hi,

I've seen code to delete duplicates. I can modify the code to copy the duplicate cell into another sheet, but how can I copy both duplicate cells and copy both rows into another sheet. I basically want to track duplicate records. Your help will be greatly appreciated.....

 

[mbarnett]

I tried the below code, but it copies everything in the sheet

Sub stance()
Dim c1 As Range, c2 As Range
Dim lRow As Long

lRow = 1
For Each c1 In Worksheets("MBS15 P&L Report").UsedRange.Columns(2).Cells

Set c2 = Worksheets("MBS15 P&L Report").UsedRange.Columns(2).Cells _
.Find(c1, LookIn:=xlValues)
If Not c2 Is Nothing Then
If c1.Value = c2.Value Then
c2.EntireRow.Copy Worksheets("Sheet1").Rows(lRow)
lRow = lRow + 1
End If
End If
Next
End Sub

 

[AnotherHiggins]

I use this:

Sub UniqueRecordsOnly()
'*****************************************************************
'   Written on:     2004-08-18
'   Written by:     John Helsabeck
'
'   Usage:          Used to isolate unique records
'*****************************************************************

Application.ScreenUpdating = False
Range(Range("a1"), Range("a1").SpecialCells(xlCellTypeLastCell)). _
    AdvancedFilter xlFilterInPlace, unique:=True
Cells.SpecialCells(xlCellTypeVisible).Copy
Orig = ActiveSheet.name
Sheets.Add
ActiveSheet.paste
ActiveSheet.name = "Unique Records From " & Orig
Sheets(Orig).ShowAllData
Range("a2").Select
Application.ScreenUpdating = True
End Sub

You can duplicate this process w/o code by going to Data > Filter > Advanced Filter > Unique records only. Then go to Edit > Go To > Special > Visible cells only. Copy'n'Paste to a new sheet.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[mbarnett]

Thanks for the code John, but I want to capture duplicates only. So, if a text is appears more than once, I want to capture the original text and the duplicate cell in another sheet.

Thanks
Mike

 

[xlbo]

1) add helper column
2) enter a COUNTIF formula to count each entry
3) autofilter on >1
4) copy everything into new sheet
5) turn off autofilter

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mbarnett]

xlbo,

thanks for the help. i used you suggestion.

 

[AnotherHiggins]

Sorry - I totally misread your original post. Glad you got it sorted.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

If you need it automating, just record yourself as you do it.....few simple amendments and it should be generic

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question