Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Princeton, New Jersey, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete unique rows, leaving only dups (Excel 97/98

    I have a very large spreadsheet containing rows of payroll data. A separate row appears for each unique combination of SS# (col A) and position code (col [img]/forums/images/smilies/cool.gif[/img]. I need to eliminate entire rows so I'm left with only those rows corresponding to people who have more than one position (i.e. there is more than one row containing their SS#). I've seen several helpful postings on how to write code to eliminate dups, but haven't been able to apply that logic to do the reverse. Any help would be much appreciated!

    Kristina

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Delete unique rows, leaving only dups (Excel 97/98

    You may not need to resort to code. Instead create an additional column in your data which could report the number of time each SS# appears in your data by using the COUNTIF function. You could then delete all rows that contain a 1 in the new column.

    Example, all you SS numbers run from A2 to A100. In a new column in row 2 enter =COUNTIF(A$2:A$100,A2), and copy that down for each row.

    You could then discard the additional column if required.

    Andrew C

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Delete unique rows, leaving only dups (Excel 97/98

    Here's a crude code solution, but it -requires- that you -first sort the data- and that you select -only- the single column and data that you want examined; don't include multiple columns or header rows in the selection:

    Sub DelUnique()
    Dim lngC As Long
    Dim intCol As Integer
    Application.ScreenUpdating = False
    intCol = Selection.Column
    For lngC = (Selection.Cells.Row + Selection.Cells.Rows.Count - 1) To Selection.Cells.Row Step -1
    If (Cells(lngC, intCol).Value <> Cells(lngC - 1, intCol).Value) And _
    (Cells(lngC, intCol).Value <> Cells(lngC + 1, intCol).Value) Then _
    Cells(lngC, intCol).EntireRow.Delete
    Next lngC
    Application.ScreenUpdating = True
    End Sub

    Not extensively tested, so back your data up. Code needs improvement.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    New Lounger
    Join Date
    Sep 2002
    Location
    Princeton, New Jersey, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete unique rows, leaving only dups (Excel 9

    Thanks John and Andrew for your great advice!

    Kristina

Posting Permissions

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