Delete Duplicates in Excel based on the value of two columns

[DrSmyth]

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?

 

[SkipVought]

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

=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

http://www.TheOfficeExperts.com

 

[SQLBI]

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.

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

http://www.solutions4msoffice.co.uk

 

[DrSmyth]

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)

 

[DrSmyth]

Cheers Leigh, wrote first response before reading your reply, will give it a go and let you know...

 

[SkipVought]

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.

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

http://www.TheOfficeExperts.com