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

1. ## 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. ## 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. ## 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.

4. ## 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
•