Cut and Paste Rows to another sheet based on duplicate value

Good Afternoon

I am looking for a macro which will compare the values in column B and if duplicates exist, cut each instance of that duplicate row and paste it in a new sheet. For example:

A B
Test 1
Test 1
Test 2
Test 3
Test 3

In this example the rows with 1 and 3 would be cut and pasted into a new sheet.

Thanks

Microsoft ApplicationsMicrosoft OfficeMicrosoft Excel

Last Comment

gowflow

8/22/2022 - Mon

etech0

1/4/2013

You can try something like this:

Sub Duplicates()
Dim x As Integer
    
Columns("A:B").Select
Range("B1").Activate
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("B1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
    .SetRange Range("A:B")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
    
x = 2
Do While Cells(x, 1).Value <> ""
    If Cells(x, 2).Value = Cells(x - 1, 2).Value Then
        Rows(x).Select
        Selection.Cut
        Sheets("NameOfSecondSheet").Select
        Range("A1").Select
        Do Until ActiveCell.Value = ""
            ActiveCell.Offset(1, 0).Select
        Loop
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Sheets("NameOfFirstSheet").Select
    End If
    x = x + 1
Loop
End Sub

Open in new window

gowflow

1/6/2013

Hi
Is this what your looking for ?
Load the file and make sure your macroes are enabled and run the button Transfer Duplicates.

Let me know
gowflow
TransferDup.xls

Washcare

1/7/2013

ASKER

gowflow

Sorry for the delay in my reply, its been a busy weekend :(

Thank you for your reply's, I am sorry but I do not think I was clear enough, I require that all the rows with duplicates are copied, including the original row. i.e.

Test 1
Test 1

Both rows would be copied to the new sheet, not just one row.

Thank you

Your help has saved me hundreds of hours of internet surfing.

fblack61

gowflow

1/7/2013

ok fine. Will this do it ?
gowflow
TransferDup.xls

Washcare

1/7/2013

ASKER

gowflow

That's a lot closer, you seem to be checking both column A and B have matching duplicates?

Could the cut and paste just work on duplicates in column B, so from you example spreadsheet (which are great, thank you) I would expect the row:

do 6

To also be cut and pasted as its the 6 which is the duplicate.

Thanks

gowflow

1/7/2013

So only duplicate in Col B ??? Not Col A as well ?
gowflow

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

gowflow

1/7/2013

Boy I was mislead and did not understand your post !!!
Chk this and let me know.
gowflow
TransferDup.xls

Washcare

1/7/2013

ASKER