Excel Duplicates

[kylecribbs]

Hello everyone,

I am new to VBA in Excel. I am more familiar with MySQL, PHP, and powershell.. so please bear with me.

I have a document with several columns and massive amounts of rows.

I need help writing a macro that will match column K with column O and find the duplicates..

For Example,
lets pretend these are rows K and O.


Jim Password Failed
Joe Authentication Failed
Rob Password Failed
Bob Password Failed
Jim Authentication Failed
Jim Password Failed
Joe Authentication Failed
Rob Password Failed
Rob Password Failed
Rob Authentication Failed
Bob Authentication Failed

I am trying to make it output (as well as delete the duplicate rows):
Jim Password Failed - 2
Jim Authentication Failed - 1
Joe Authentication Failed - 2
Rob Password Failed - 3
Bob Password Failed - 1
Rob Authentication Failed - 1
Bob Authentication Failed - 1

The rows are going to be unknown.. so for example Bob will not be a known name to search for. I am trying to make it populate based upon the information inside the document. And also putting it in ABC order would help too..

I have no idea where to start on this...

[p45cal]

Pivot table. See attached, a guess at you scenario. Pivot table at cell E15 adjusted to match your required output (red border) but it can be prettier, and on a different sheet.
You can remove duplicates using the built-in duplicate remover but then you'd lose the count in the pivot table.

[Bob Phillips]

How about putting a formula in an adjacent cell

=COUNTIF($A$1:$A$20,$A1)

and copy down, and then use

Data>Data Tools>Remove Duplicates on the 2 columns

[PhilB]

Hi kylecribbs

I've attached a file for you to play with. It sorts column K into ascending order & checks for duplicate cells down the range, it displays a message box telling you how many duplicates were found & gives you the option to delete them. Choose Yes to delete, No to end the routine & manually check them. They'll be highlighted in red. To delete, re run the routine & press Yes

I use the code in other workbooks & adapted it to hopefully suit your needs
Give it a go & let me know

Cheers
Phil

[kylecribbs]

I sat around and thought about it and I wrote something.. but it is very intermittenet. If I run it once.. it misses some duplicates.. but if I run it again it catches them.. as well as a third time.. let me know what you think of the code.

also bear in mind that I wrote this by hand.. I am unable to copy and paste the code directly..

Sub Alert()

begingRow = 2
endRow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCnt = begingRow to endRow

I = 1
beginRow2 = RowCnt + 1
endRow2 = Cells(Rows.Count, "A").End(xlUp).Row
rowKVal = Cells(RowCnt, "K").Value
rowMVal = Cells(RowCnt, "M").Value
rowOVal = Cells(RowCnt, "O").Value
rowPVal = Cells(RowCnt, "P").Value
rowAColor = Cells(RowCnt, "A").Interior.ColorIndex

If InStr(rowOVal, "gnome") > 0 or InStr(rowPVal, "gnome") > 0 Then
Rows(RowCnt).EntireRow.Delete
End If 
If InStr(rowOVal, "screensaver") > 0 or InStr(rowPVal, "screensaver") > 0 Then
Rows(RowCnt).EntireRow.Delete
End If
If InStr(rowOVal, "gnome-screensaver") > 0 or InStr(rowPVal, "gnome-saver") > 0 Then
Rows(RowCnt).EntireRow.Delete
End If
If rowAColor = 16 Then
Rows(RowCnt).EntireRow.Delete
End If

For RowCnt2 = beginRow2 to endRow2 

If InStr(Cells(RowCnt2, "K"), rowKVal) > 0 and InStr(Cells(RowCnt2, "M"), rowMVal) > 0 Then

If Cells(RowCnt2, "S").Value > 0 Then
I = I + Cells(RowCnt2, "S").Value - 1
Else
I = I + 1
End If
Rows(RowCnt2).EntireRow.Delete

EndIf
Next RowCnt2

Cells(RowCnt, "S").Value = I

Next RowCnt

End Sub

[p45cal]

It misses some because as you're moving down the rows checking each one, when you delete one, all the rows below that move up one row, so really you'd need to check that same row number all over again. Solution, instead of running down the rows, run up the rows, change the likes of:
For RowCnt = begingRow To endRow
to:
For RowCnt = endRow to begingRow step -1

You're also in danger of deleting a row twice with your multiple if statements.
You don't need this one:
If InStr(rowOVal, "gnome-screensaver") > 0 Or InStr(rowPVal, "gnome-saver") > 0 Then
it will have been caught by the first one.

You can string all the conditions in one line:
If InStr(rowOVal, "gnome") > 0 Or InStr(rowPVal, "gnome") > 0 Or InStr(rowOVal, "screensaver") > 0 Or InStr(rowPVal, "screensaver") > 0 Or rowAColor = 16 Then Rows(RowCnt).EntireRow.Delete