Finding duplicates in a set of data can be pretty daunting at some points. I thought I’d share some of my knowledge, since I work with large sets of data everyday, and finding duplicates is something I have had to figure out.
First, let me show you how to find duplicate rows in a MySQL table. It’s actually quite easy. Just throw this statement in, replace t with the name of the table your looking in, and col with the field your looking for dupes in.
SELECT col, count(*)
FROM t
GROUP BY col,
HAVING count(*) > 1
Now, let me give you a VBA macro that you can use in Excel to find duplicate data. This macro is a good one to have saved in your PERSONAL.xls file, so that it is available in all your Excel files. You need to first sort the column, then select just the block of data you want to look for dupes in, and then run this macro… it will replace all the dupes (except the first one) with —. Then you can re-sort all your data by that column, and all the duplicate rows will be grouped together where the data was replaced with —.
Sub DeDupe()
'
' DeDupe Macro shared by FrankBaris.com
'
Dim RowNdx As Long
Dim ColNum As Integer
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value Then
Cells(RowNdx, ColNum).Value = "----"
End If
Next RowNdx
End Sub
I hope these help some of you out, as I know, trying to figure these out on your own can really hurt ones brain :-0