Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find and delete duplicates (2003)

    I have merged three spreadsheets into one and now I need to delete all the rows with duplicates in column C (serial number).
    I'm attaching a short sample of the file. The records I need to keep are the ones that contain information in the description field.
    The spreadsheet has around 3K rows in it, and we need to do this every month. So what is the easiest way to do this. One note is that the serial number is still considered a duplicate if it is preceeded by zeros (for example, C2 and C3 are considered to be the same).

    If you suggest code, please tell me how to use it - where to copy it, etc. Thank YOU!!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find and delete duplicates (2003)

    You can store the macro described below in a workbook especially created for this purpose, or in your Personal.xls macro workbook (see Legare Coleman's <!post=Personal.xls Tutorial (All),118382>Personal.xls Tutorial (All)<!/post> if you don't know what that is). In either case:

    - Activate the Visual Basic Editor (Alt+F11)
    - Click on the workbook in which you want to store the code in the treeview on the left hand side.
    - Select Insert | Module
    - Copy and paste the macro into the module.

    To run the code:
    - If you stored the code in a dedicated workbook, open this workbook
    - Open the workbook with the merged data
    - Make sure that the worksheet with the data is the active sheet
    - Select Tools | Macro | Macros...
    - Select FilterUnique
    - Click Run

    <img src=/w3timages/blueline.gif width=33% height=2>

    Sub FilterUnique()
    Dim lngRow As Long
    Dim lngMaxRow As Long

    lngMaxRow = Range("C65536").End(xlUp).Row
    Range("H1") = "Temp"
    Range("H2:H" & lngMaxRow).FormulaR1C1 = _
    "=IF(ISERROR(VALUE(RC<!t>[-5]<!/t>)),RC<!t>[-5]<!/t>,VALUE(RC<!t>[-5]<!/t>))"

    Range("A1").CurrentRegion.Sort Key1:=Range("H1"), _
    Key2:=Range("D1"), Header:=xlYes

    For lngRow = lngMaxRow To 2 Step -1
    If Range("H" & lngRow) = Range("H" & (lngRow - 1)) Then
    Range("H" & lngRow).EntireRow.Delete
    End If
    Next lngRow

    Range("H1").EntireColumn.Delete
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •