Delete duplicate rows of data (PLEASE HELP)

[tigerax]

Hi - I've never posted on one of these forums before, just managed to find the answers by searching for questions other people have asked, although I cant seem to find an answer to my specific problem. so hoping someone can help.

I have some extracts of data in an excel spreadsheet where in column A it contains a computer name.

For example: MYPCA
MYPCB

In column B it contains all the software installed on that computer. For each time software is listed in column B, Column A also displays the computer name next to it.

My problem is that some software in column B is duplicating and I want to run a Macro or some code to check column B for duplicates and then delete the row.

The bit that is troubling me to find an answer is that some of the software installed is going to be installed the same on each computer in my list in column A. I dont want to delete those duplicates; just the duplicates appearing twice for each item in column A.

I hope this makes sense: I'm sure the resolution is quite simple but I've been working so long on other parts of my project that my head is frazzled!

Many thanks in Advance.

tigerax

 

[Krishnakumar]

Hi,

OK. Try this one.

Sub kTest()

    Dim ka, i As Long, k(), n As Long, strConcat As String
    
    With Sheets("Software")
        ka = Intersect(.UsedRange, .Range("A:B"))
    End With
    
    ReDim k(1 To UBound(ka, 1), 1 To 2)
    
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 1 To UBound(ka, 1)
            strConcat = ka(i, 1) & "|" & ka(i, 2)
            If Not .exists(strConcat) Then
                n = n + 1
                k(n, 1) = ka(i, 1)
                k(n, 2) = ka(i, 2)
                .Add strConcat, Nothing
            End If
        Next
    End With
    If n Then
        Sheets("Software").[d1].Resize(n, 2).Value = k
    End If

End Sub

HTH

 

[tigerax]

.

 

[tigerax]

Thank you so much. exactly what I needed

 

[Jomu]

This shoul help You automaticaly. That deletes double rows on DAT table columns A to G. That codes Huuhaa workssheet and copy the data there DAT table and perform special filtering and copy the filtered data table, DAT, and finally delete the table Huuhaa. Hope that helps You.

Sub delete_duplicates)
Dim name As String
On Error Resume Next
Application.DisplayAlerts = False
name = Worksheets("Huuhaa").Name
If Not Err.Number = 0 Then Sheets.Add.Name = "Huuhaa"
Sheets("Huuhaa").Cells.ClearContents
On Error GoTo 0
Sheets("DAT").Activate
Cells.Copy Sheets("Huuhaa").Range("A1")
Cells.ClearContents
Sheets("Huuhaa").Columns("A:G").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("A6"), Unique:=True
Sheets("Huuhaa").Delete
Application.DisplayAlerts = True
End Sub