none
How to Delete and tract total number of duplicates RRS feed

  • Question

  • Hello, 

    I have a file with 15 columns and over 3000 rows, I am trying to have duplicates removed but keep track in column P of the total count of duplicates in column A.  is there a formula or macro that will make this possible?  

    A B C D E F G H I J K L M N O P Count
    123456 0 0 0 0 0 0.00 0.00 0 0.00 0.00 0.00 0 0.00 0.00%
    123456 0 0 0 0 0 0.00 0.00 0 0.00 0.00 0.00 0 0.00 0.00%
    123456 0 0 0 0 0 0.00 0.00 0 0.00 0.00 0.00 0 0.00 0.00%
    456789 0 0 0 0 0 0.00 0.00 0 0.00 0.00 0.00 0 0.00 0.00%
    789456 0 0 0 0 0 0.00 0.00 0 0.00 0.00 0.00 0 0.00 0.00%
    789456 0 0 0 0 0 0.00 0.00 0 0.00 0.00 0.00 0 0.00 0.00%
    695874 0 0 0 0 0 0.00 0.00 0 0.00 0.00 0.00 0 0.00 0.00%
















    A B C D E F G H I J K L M N O P Count
    123456 0 0 0 0 0 0.00 0.00 0 0.00 0.00 0.00 0 0.00 0.00% 3
    456789 0 0 0 0 0 0.00 0.00 0 0.00 0.00 0.00 0 0.00 0.00% 1
    789456 0 0 0 0 0 0.00 0.00 0 0.00 0.00 0.00 0 0.00 0.00% 2
    695874 0 0 0 0 0 0.00 0.00 0 0.00 0.00 0.00 0 0.00 0.00% 1


















    I would appreciate any help I can get, 

    Regards,

    MariCriss


    • Edited by MariCriss Saturday, July 11, 2015 11:17 AM
    Saturday, July 11, 2015 11:12 AM

Answers

  • Hi,

    >>the only column that will have duplicates is column A (Account numbers).

    Since the only column that will have duplicates is Column A, just compare the value in Column A is OK. try the code below :

    Sub TotalRows()
     Dim Row1 As Range
     Dim Row2 As Range
    For i = 2 To Sheets(1).UsedRange.Rows.Count
    RowCount = 1
    For j = i + 1 To Sheets(1).UsedRange.Rows.Count
    Set Row1 = Sheets(1).Rows(i)
    Set Row2 = Sheets(1).Rows(j)
    flag = CompareRows(Row1, Row2)
    If flag = True Then
    Rows(j).Delete
    RowCount = RowCount + 1
    End If
    Next j
    If IsEmpty(Cells(i, 1).Value) = False Then
    Sheets(1).Cells(i, 16).Value = RowCount
    End If
    Next i
    End Sub
    ' Compare the rows
    Function CompareRows(Row1 As Range, Row2 As Range)
    If Row1.Columns(1).Value = Row2.Columns(1).Value Then
    CompareRows = True
    Else
    CompareRows = False
    End If
    End Function

    Hope this couldhelp you.

    Best Regards,

    lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Monday, July 20, 2015 4:02 PM
    Tuesday, July 14, 2015 9:08 AM
    Moderator

All replies

  • There are several approaches, one way without macros -

    If it isn't already make the data a Table

    If col-P is not already in the Table add an extra column, and then one more column for col-Q

    in the first row of the table in col-P, which for me after row headers were included is P2

    =COUNTIF([Column1],Table1[[#This Row],[Column1]])-1

    The formula should automatically copy down, giving the number of duplicates in the first column, for me Col-A, zero if no duplicates

    Paste as values the duplicate values into an adacent column to the right in the Table

    With the table selected, Data, Data Tools, Remove Duplicates

    you'll probably want to 'Unselect' all the columns then tick the first column.

    Hit OK

    The countIf formulas should revert to zero's leaving the previous duplicate values in you pasted. 

    If this is a process you'll be repeating, add yet a third column, and a simple formula to add the current duplicates to the previous dupe's. When about to "Delete Duplicates" again, paste totals of current and previous duplicates into the previous duplicates column.

    Hope that all makes sense!

    Edit: forgot to say the reason to CounIt formula should return zero if no dupe's is because you don't want to keep adding a 1 if no dupes to the previous total. So depending you your needs you may want your actual total as =(dupes + 1)
    Saturday, July 11, 2015 1:10 PM
    Moderator
  • Hi,

    >>I am trying to have duplicates removed but keep track in column P of the total count of duplicates in column A?

    you may finish this job ny following the below code,

    Sub TotalRows()
     Dim Row1 As Range
     Dim Row2 As Range
    For i = 2 To Sheets(1).UsedRange.Rows.Count
    RowCount = 1
    For j = i + 1 To Sheets(1).UsedRange.Rows.Count
    Set Row1 = Sheets(1).Rows(i)
    Set Row2 = Sheets(1).Rows(j)
    flag = CompareRows(Row1, Row2)
    If flag = True Then
    Rows(j).Delete
    RowCount = RowCount + 1
    End If
    Next j
    If IsEmpty(Cells(i, 1).Value) = False Then
    Sheets(1).Cells(i, 16).Value = RowCount
    End If
    Next i
    End Sub
    ' Compare the rows
    Function CompareRows(Row1 As Range, Row2 As Range)
    For i = 1 To 15
    If Row1.Columns(i).Value = Row2.Columns(i).Value Then
    CompareRows = True
    Else
    CompareRows = False
    Exit For
    End If
    Next i
    End Function

    Hope this could help you.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, July 13, 2015 8:08 AM
    Moderator
  • Hi Lan, 

    Sorry, I double checked and it is not deleting the duplicates. each row has different data, the only column that will have duplicates is column A (Account numbers). The macro is counting; however, I am only getting one and duplicates remain. 

    Regards,

    MariCriss



    • Edited by MariCriss Tuesday, July 14, 2015 6:19 AM
    Tuesday, July 14, 2015 5:20 AM
  • Hi,

    >>the only column that will have duplicates is column A (Account numbers).

    Since the only column that will have duplicates is Column A, just compare the value in Column A is OK. try the code below :

    Sub TotalRows()
     Dim Row1 As Range
     Dim Row2 As Range
    For i = 2 To Sheets(1).UsedRange.Rows.Count
    RowCount = 1
    For j = i + 1 To Sheets(1).UsedRange.Rows.Count
    Set Row1 = Sheets(1).Rows(i)
    Set Row2 = Sheets(1).Rows(j)
    flag = CompareRows(Row1, Row2)
    If flag = True Then
    Rows(j).Delete
    RowCount = RowCount + 1
    End If
    Next j
    If IsEmpty(Cells(i, 1).Value) = False Then
    Sheets(1).Cells(i, 16).Value = RowCount
    End If
    Next i
    End Sub
    ' Compare the rows
    Function CompareRows(Row1 As Range, Row2 As Range)
    If Row1.Columns(1).Value = Row2.Columns(1).Value Then
    CompareRows = True
    Else
    CompareRows = False
    End If
    End Function

    Hope this couldhelp you.

    Best Regards,

    lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Monday, July 20, 2015 4:02 PM
    Tuesday, July 14, 2015 9:08 AM
    Moderator