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

 

[thunder_anger]

Welcome

can you post a sample of your data to clarify the idea?

 

[tigerax]

Welcome

can you post a sample of your data to clarify the idea?

<TABLE style="WIDTH: 423pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=563 x:str><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 341pt; mso-width-source: userset; mso-width-alt: 16603" width=454><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 82pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 vAlign=top width=109 align=left><?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t201 path="m,l,21600r21600,l21600,xe" o:spt="201" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><vath o:connecttype="rect" fillok="f" strokeok="f" o:extrusionok="f" shadowok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock shapetype="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="Z-INDEX: 2; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 84.75pt; HEIGHT: 12.75pt; VISIBILITY: hidden; MARGIN-LEFT: 0px" id=_x0000_s2050 o:insetmode="auto" stroked="f" type="#_x0000_t201"><o:lock v:ext="edit" text="t" rotation="t"></o:lock></v:shape><v:shape style="Z-INDEX: 3; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 84.75pt; HEIGHT: 12.75pt; VISIBILITY: hidden; MARGIN-LEFT: 0px" id=_x0000_s2051 o:insetmode="auto" stroked="f" type="#_x0000_t201"><o:lock v:ext="edit" text="t" rotation="t"></o:lock></v:shape><v:shape style="Z-INDEX: 4; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 84.75pt; HEIGHT: 12.75pt; VISIBILITY: hidden; MARGIN-LEFT: 0px" id=_x0000_s2052 o:insetmode="auto" stroked="f" type="#_x0000_t201"><o:lock v:ext="edit" text="t" rotation="t"></o:lock></v:shape><v:shape style="Z-INDEX: 5; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 84.75pt; HEIGHT: 12.75pt; VISIBILITY: hidden; MARGIN-LEFT: 0px" id=_x0000_s2053 o:insetmode="auto" stroked="f" type="#_x0000_t201"><o:lock v:ext="edit" text="t" rotation="t"></o:lock></v:shape><TABLE cellSpacing=0 cellPadding=0><TBODY><TR><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 82pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=109>ServerName</TD></TR></TBODY></TABLE></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 341pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=454>DisplayName</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>EULONNBLF1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Windows Genuine Advantage Validation Tool (KB892130)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>EULONNBLF1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Windows Genuine Advantage Validation Tool (KB892130)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>EULONPCTEST2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">SoundMAX</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>EULONPCTEST2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">WebFldrs XP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>EULONPCTEST2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Windows Genuine Advantage Validation Tool (KB892130)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>EULONPCTEST2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Windows Genuine Advantage Validation Tool (KB892130)





</TD></TR></TBODY></TABLE>

Sorry if this is wrong way of posting data. But as a sample for listing software. One PC name shows two bit of data showing Windows Genuine Advantage Validation Tool (KB892130) and so does another PC. I want it to only display one entry for each servername.

Column A is server name and column B is display name. I'm going to have about 60 different computer names in the spreadsheet showing the software installed for each one.

Hope this makes it abit more clearer. Any help is appreciated!

tigerax

 

[ColinKJ]

Hi tigerax,

I did an example WB named Delete Duplicates for another forum user, which you can download from:

http://www.box.net/shared/ojl13ggam9

You would need to change the columns being compared, but otherwise it would be ok.

 

[tigerax]

Hi tigerax,

I did an example WB named Delete Duplicates for another forum user, which you can download from:

http://www.box.net/shared/ojl13ggam9

You would need to change the columns being compared, but otherwise it would be ok.

Thanks - This is the code in the spreadsheet you attached.

R = Sheets(1).Range("C65536").End(xlUp).Row
For a = 2 To R
If Cells(a, 3) = "" Then GoTo nexta
RR = Sheets(1).Range("C65536").End(xlUp).Row
For b = a + 1 To RR
x:
If Cells(b, 3) = "" Then GoTo nexta
For c = 1 To 60
If Cells(a, c) <> Cells(b, c) Then GoTo nextb
Next c
'We have a duplicate row so delete it
Rows(b & ":" & b).Select
Selection.Delete Shift:=xlUp
GoTo x
nextb:
Next b
nexta:
Next a
Cells(1, 1).Select


I dont know how to make it work with my data though. I only have data in column A and B and dont fully understand the code Still learning bits as i go along. My sheetname is 'software'

Any chance you could tidy up the code for me to suit my sheet?

I'll put you on the good persons list for Christmas. I'm friends with Santa

 

[Krishnakumar]

Hi,

Are you on XL 2007 or XL 2010 ? If so,

Select the data > Go to Data > Remove Duplicates

HTH

 

[tigerax]

None of the above. Excel 2003. My company is behind the times!

 

[Krishnakumar]

Hi,

Try this code.

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)
            End If
        Next
    End With
    If n Then
        Sheets("Software").[d1].Resize(n, 2).Value = k
    End If

End Sub

HTH

 

[tigerax]

Hi, thanks for taking the time to try. Unfortunately it didnt work.

It didnt delete the duplicates in column B. it just copied the data exactly as it is to columns D and E.

tigerax

 

[ColinKJ]

Hi tigerax,

I've put together an example WB based on the info you provided named Delete Duplicates 3, which you can download from:

http://www.box.net/shared/qhcilr0vto

Ctrl+q activates the macro.

To run the test again, you can restore the data from sheet2.