Solved

Copy duplicate rows to new sheet

Posted on 2014-03-25
5
2,768 Views
Last Modified: 2014-03-26
Could an expert provide me with VBA to move duplicates please.

I have a spreadsheet that has a header row in row 1

columns go from A to T

Number of rows will change

I need to look in column H for any duplicates and wherever I find one I need to move all of the items [complete row] to a new worksheet. [Named Duplicates] I also need the header row from Sheet1 copied onto the sheet named Duplicates.

So, if in Cell H3 I find abcdef and abcdef is also in H4 I need to move both rows to the sheet named duplicates

if in Cell H3 I find abcdef and abcdef is also in H4 and H5 and H6 I need to move all rows to the sheet named duplicates

Many thanks
0
Comment
Question by:Jagwarman
  • 2
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
Comment Utility
Here it is:

Sub MoveDups()
Dim lLastRow As Long, lRowLoop As Long

lLastRow = Cells(Rows.Count, 1).End(xlUp).Row

Range("u2:u" & lLastRow).FormulaR1C1 = "=countif(R2C8:R" & lLastRow & "C8,RC8)"
Range("u2:u" & lLastRow).NumberFormat = "0"

With Range("A1:u" & lLastRow)
    .AutoFilter
    .AutoFilter field:=21, Criteria1:=">1"
    .Resize(, 20).SpecialCells(xlCellTypeVisible).Copy Sheets("Duplicates").Cells(1, 1)
    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter
End With

Columns("U").Delete

End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Have we not seen this same question before, just recently???

Other option would be to use Advanced Filter function to separate unique values to their own sheet.

Thanks
Rob H
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
0
 

Author Comment

by:Jagwarman
Comment Utility
The previous question was only to move one of the duplicates this is to move all items where there is a duplicate

so similar but different
0
 

Author Closing Comment

by:Jagwarman
Comment Utility
Exactly what I wanted
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now