[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

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
0
Washcare
Asked:
Washcare
  • 6
  • 4
1 Solution
 
etech0Commented:
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

0
 
gowflowCommented:
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
0
 
WashcareAuthor Commented:
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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
gowflowCommented:
ok fine. Will this do it ?
gowflow
TransferDup.xls
0
 
WashcareAuthor Commented:
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
0
 
gowflowCommented:
So only duplicate in Col B ??? Not Col A as well ?
gowflow
0
 
gowflowCommented:
Boy I was mislead and did not understand your post !!!
Chk this and let me know.
gowflow
TransferDup.xls
0
 
WashcareAuthor Commented:
gowflow

Yes only duplicates in column B. Thanks for the example, this still leaves the following row:

go              6

on Sheet1?

Thanks
0
 
gowflowCommented:
wow that was not an easy one.
try this I hope we got it.
gowflow
TransferDupFinal.xls
0
 
WashcareAuthor Commented:
gowflow

Many thanks, works great.

Regards
0
 
gowflowCommented:
Finnalllllyyy !! shame on me so simple request that took me several attempt to adapt correctly. Glad I could at least help you. !!!
gowflow
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now