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
Printable View
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
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
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.
personally i would be using ADO with sql query like
you can then use the range copyfromrecordset method to copy all to the new workbook at the specified range, probably column A, next empty rowCode: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 also use the distinct keyword in the sql, to only return one record for each mode of transport
requires reference to ADO
Ah ok thanks! I will give it a go and see if it works.