Duplicate cells

user_18391348 0
Viewed 68

How can I eliminate duplicate data in excel without aggregating them all (keeping the first data and eliminating the rest)? Example column A:

Line 1: John

Line 2: John

Line 3: John

Line 4: Philip

Line 5: Philip

Line 6: Tim

Line 7: Tim

Line 8: Tim

How can I get just the lines 1,4 and 6, and the rest with blank cells?

So that it looks like this:

Line 1: John

Line 2:

Line 3:

Line 4: Philip

Line 5:

Line 6: Tim

Line 7:

Line 8:

Is it possible also by vba code?

Many thanks!

duplicates vba excel
2 Answers

Remove Group Duplicates

  • For the range A1:ALastRow, it is 'mimicking' copying the formula =IF(A2=A1,"",A2) e.g. into the range B2:BLastRow, copying the values to the range A2:ALastRow and then clearing the range B2:BLastRow.
Option Explicit

Sub RemoveGroupDuplicates()
    With ActiveSheet.Range("A1").CurrentRegion.Columns(1)
        With .Resize(.Rows.Count - 1).Offset(1)
            .Value = .Worksheet.Evaluate("IF(" & .Address & "=" _
                & .Offset(-1).Address & ",""""," & .Address & ")")
        End With
    End With
End Sub
user_98140690

You could enter the following formula in column B and then paste the result as values over the original data:

=IF(COUNTIF($A$1:$A1,$A1)=1,A1,"")

Enter this into B1 and fill it down.

user_47425700
Related