Copy duplicates to new spreadsheet?

[Nightwalker83]

Hi,

Anyone know how to find duplicates in a excel spreadsheet then copy the duplicate records to a new sheet leaving the original intact through code?

Thanks,


Nightwalker

[westconn1]

what part of the row makes it a duplicate?
one column, some columns, all columns?

very easy to copy all rows without any duplicates occuring to another sheet

please give more details on your requirements

post a sample workbook

[Nightwalker83]

In this example I have attached column "C" has modes of transport and some of those are the same as previous entries in that column what I want to do is highlight all the rows with are the same, example that have having the transportation mode "Car" and copy those entire rows to a new work sheet.

I only have Office 2003 at home but at work we use Office 2010 which has the ability to remove duplicate records from the spreadsheet but I need to copy the duplicates to a new spreadsheet instead. This because I have a large number of records to look through around 30, 000 and filter out the most recent in terms of business.

[westconn1]

personally i would be using ADO with sql query like

Dim cn As Connection, rs As Recordset, rs2 As Recordset, cel As Range

Set cn = New Connection
Set rs = New Recordset
Set rs2 = New Recordset
With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";" & _
"Extended Properties='Excel 8.0;hdr=no;IMEX=1;'"
    .Open
End With
sql = "select * from [sheet1$] where f3 = 'Car'"    ' f3 being field 3 (column C) if the sheet has headers, you can use the header as field name, but change the connectionstring appropriately 
rs.Open sql, cn, adOpenStatic, adLockReadOnly    ' 3 records return from the sample workbook

you can then use the range copyfromrecordset method to copy all to the new workbook at the specified range, probably column A, next empty row

you can also use the distinct keyword in the sql, to only return one record for each mode of transport

requires reference to ADO

[Nightwalker83]

Ah ok thanks! I will give it a go and see if it works.