Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Delete Duplicates in Excel based on the value of two columns

Status
Not open for further replies.

DrSmyth

Technical User
Jul 16, 2003
557
GB
Hi,

I've done a keyword search on deleting duplicates in this forum and i've found a few responses, but i've not found anything that covers the problem i'm currently facing.

I need to run a macro that will hunt for duplicates in column A of an Excel spreadsheet and i need it to delete the row where column N is blank. EG:

Column A Column N
a
b
a Y
c
d
d N
This should read:
Column A Column N
a Y
b
c
d N

Can anybody help?
 
Doc,

This can be done without VBA if you like.

1. Sort by columns A/N -- need to do this regardless of VBA or not.

2. create an expression to test a duplicate in column A and BLANK in N and copy down you list IN THE ADJACENT COLUMN
Code:
=IF(AND(OR(A2=A3,A2=A1),ISBLANK(N2)),1,0)
3. copy and paste special - values the new formula COLUMN

4. sort you r list on the new column

5. selete the rows with 1 in the new column

6. restore the order of your list.

;-)

Skip,
Skip@TheOfficeExperts.com
 
DrSmythe,

First, Sort your data by Column1 and by column2, both ascending.

Assuming that your Column1 is 'A' and starts at Row1, copy and paste this code into a new module and run the code.

Code:
Sub RemoveDups()

Dim strA As String

Sheet1.Select
Cells(1, 1).Select

strA = ActiveCell.Value

    Do While IsEmpty(ActiveCell) = False
    
            If ActiveCell.Value = strA And IsEmpty(ActiveCell.Offset(0, 1)) = True Then
            
                ActiveCell.EntireRow.Delete
            
            Else
                
                strA = ActiveCell.Value
                ActiveCell.Offset(1, 0).Select
            
            End If
    
    Loop

End Sub

Hope this helps.

Leigh Moore
Solutions 4 MS Office Ltd
 
Cheers Skip, a vba route may be more appropiate though as i won't actually be physically doing anything to the spreadsheet.

Basically the spreadsheet is an output from Business Objects, I'm running another spreadsheet after this output has been done to perform the formatting (This is where i will be adding the code)
 
Cheers Leigh, wrote first response before reading your reply, will give it a go and let you know...
 
Leigh & Doc,

When delecting rows, ALWAYS start from the bottom up, although using the Select method avoids th problem of loosing track (which happens using other top down techniques), Select methods slow down the procedure.
Code:
Sub nn()
    With UsedRange
      r1 = .Row
      r2 = r1 + .Rows.Count - 1
    End With
    For lRow = r2 To 1 Step -1
      With Cells(lRow, 1)
        If .Value = .Offset(1, 0).Value And .Offset(0, [N1].Column - 1).Value = "" Then
          .EntireRow.Delete shift:=xlUp
        End If
      End With
    Next
End Sub
:)

Skip,
Skip@TheOfficeExperts.com
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top