Forum Discussion

NicoleSager's avatar
NicoleSager
Copper Contributor
May 02, 2023

How to Delete Duplicate Cells per Row

Hello everyone!

 

I am working on a file that has about 1000 rows. Column A is a Unique ID (Contact ID) and each column afterward is an Entity ID that will be tagged/linked to the Unique ID in column A. There are cases where there are duplicate Entity IDs per Unique ID and I need to remove the duplicate except the first instance. 

 

I found a macro code online however it looks to delete ALL duplicates not leaving the first instance. Could someone help me with fixing this? Or maybe there is another way to do this?

Code:

 

Sub DeleteDuplicates()

Dim MyRange As Range, rw As Range
Dim MyArray() As Variant
Dim aryEntry() As String, aryFilter() As String
Dim MyFilter As String, MyIndex As Integer
Dim i As Integer

Set MyRange = ActiveSheet.UsedRange
LastColumn = MyRange.Columns.Count

ReDim aryEntry(1 To LastColumn)
ReDim aryFilter(1 To 1)

For Each rw In MyRange.Rows
    'Load row into the output array
    MyArray = rw.Cells
    
    'Combines cell values and column index in a single array index
    For i = 1 To UBound(MyArray, 2)
        aryEntry(i) = MyArray(1, i) & " " & i
    Next i

    For i = 1 To UBound(MyArray, 2)
        'Filter only on the cell value, not the index
        MyFilter = Left(aryEntry(i), InStr(1, aryEntry(i), " ") - 1)
        
        'Loads duplicate cell values (with column index) into an array
        aryFilter = Filter(aryEntry, MyFilter)
        
        'If more than one entry exists, it is a duplicate
        If UBound(aryFilter) > 0 Then
            
            'Separates index from cell value
            MyIndex = Right(aryEntry(i), Len(aryEntry(i)) - InStr(1, aryEntry(i), " "))
            
            'Clear all duplicate entries (but not original)
            For j = 1 To UBound(aryFilter)
                MyArray(1, MyIndex) = ""
            Next j
        End If
    Next i
    
    'Returns cleaned array to the worksheet
    rw.Cells = MyArray
Next rw

'Deletes all blank cells in the used range
MyRange.SpecialCells(xlCellTypeBlanks).Delete (xlShiftToLeft)

End Sub

 

Thank you!!

  • NicoleSager 

    An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

  • Joe_official4's avatar
    Joe_official4
    Copper Contributor
    Go to Data >>> Data Tools>>>>>Remove Duplicates>>>Check "My data has headers'>>>Tick the header to run duplicate on
    • NicoleSager's avatar
      NicoleSager
      Copper Contributor
      That will remove duplicates by column not row unfortunately
  • NicoleSager 

    An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

    • NicoleSager's avatar
      NicoleSager
      Copper Contributor
      Oh this seems to do the job, thank you so much! Would you mind explaining how you built it? I would love to be able to utilize this going forward.

      Thanks again!
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor

        NicoleSager 

        You can start the Power Query Editor as shown in the screenshot. Power Query -> Editor starten (start editor in english Excel). 

        In the Power Query Editor you can follow the steps starting with "Quelle" or "source" in english. You can select any step you want. Currently the step "Quelle" (source) is selected. All the buttons and applied steps are shown in your language when you open the file. On the right side i've indicated the gear icons with blue dashes. You can click on a gear icon to view which rule has been applied to create the change of the data.

         

  • ItsBhatti's avatar
    ItsBhatti
    Brass Contributor

    NicoleSager 

    To delete duplicate cells per row, you can use a combination of Excel functions and tools. Here are the steps:

    1. Select the range of cells that you want to check for duplicates.
    2. Click on the "Conditional Formatting" option in the "Home" tab of the ribbon.
    3. Select "Highlight Cells Rules" and then "Duplicate Values" from the dropdown menu.
    4. In the "Duplicate Values" dialog box, select the "Duplicate" option and choose a formatting style for the duplicate cells. Click OK.
    5. You will now see the duplicate cells highlighted in your selected range.
    6. Select the entire range again and go to the "Data" tab on the ribbon.
    7. Click on the "Remove Duplicates" option in the "Data Tools" group.
    8. In the "Remove Duplicates" dialog box, make sure that only the columns you want to check for duplicates are selected. Click OK.
    9. You will now see a prompt asking you to confirm the deletion of the duplicate cells. Click OK to remove them.

    This should delete all duplicate cells per row within the selected range.

Resources