Finding and removing duplicates values from row

Question

I am working in Excel 2007. I have data as per following:

data|-v1-|-v2-|-v2-|-v3-|-v4-|
------------------------------
1 |a | |a | |b |

2 | |c |d | | |

3 |e |e |e |e |e |

I am trying to remove duplicate values from each row. I am doing it manually. Expected output is like this:

data|-v1-|-v2-|-v2-|-v3-|-v4-|
------------------------------
1 |a | | | |b |

2 | |c |d | | |

3 |e | | | | |

How to do this in a quick way?

This may well be a duplicate of this: http://stackoverflow.com/questions/22082680/remove-duplicate-cells-in-a-row — Petay87, Jan 08 '15 at 12:41

score 0 · Accepted Answer · edited May 23 '17 at 11:51

As already outlined by another user HERE, The following code should resolve this issue for you.

Sub RemoveDuplicatesInRow()

    Dim lastRow As Long
    Dim lastCol As Long
    Dim r As Long 'row index
    Dim c As Long 'column index
    Dim i As Long

    With ActiveSheet.UsedRange
        lastRow = .Row + .Rows.Count - 1
        lastCol = .Column + .Columns.Count - 1
    End With

    For r = 1 To lastRow
        For c = 1 To lastCol
            For i = c + 1 To lastCol 'change lastCol to c+2 will remove adjacent duplicates only
                If Cells(r, i) <> "" And Cells(r, i) = Cells(r, c) Then
                    Cells(r, i) = ""
                End If
            Next i
        Next c
    Next r

End Sub

score 0 · Answer 2 · answered Jan 08 '15 at 12:59

I think possible (untested) with a formula rule like =COUNTIF($B2:B2,B2)>1 in Conditional Formatting and then filtering by colour (once each for four columns) to blank out the CF formatted values. (Or maybe leave the values as they are and just apply formatting to blend the duplicates into the background!)

score 0 · Answer 3 · answered Jan 08 '15 at 14:26

Consider:

Sub ClearDuplicatesByRow()
    Dim nLastRow As Long, nLastColumn As Long
    Dim nFirstRow As Long, nFirstColumn As Long
    Dim i As Long, j As Long, wf As WorksheetFunction
    Dim rLook As Range, rWhat As Variant

    ActiveSheet.UsedRange
    Set r = ActiveSheet.UsedRange
    Set wf = Application.WorksheetFunction
    nLastRow = r.Rows.Count + r.Row - 1
    nLastColumn = r.Columns.Count + r.Column - 1
    nFirstRow = r.Row
    nFirstColumn = r.Column

    For i = nFirstRow To nLastRow
        For j = nFirstColumn + 1 To nLastColumn
            Set rLook = Range(Cells(i, nFirstColumn), Cells(i, j - 1))
            rWhat = Cells(i, j).Value
            If wf.CountIf(rLook, rWhat) > 0 Then
                Cells(i, j).ClearContents
            End If
        Next j
    Next i
End Sub

It will clear duplicates even if they are not adjacent.

Finding and removing duplicates values from row

3 Answers3