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!
CodePudding user response:
Remove Group Duplicates
- For the range
A1:ALastRow
, it is 'mimicking' copying the formula=IF(A2=A1,"",A2)
e.g. into the rangeB2:BLastRow
, copying the values to the rangeA2:ALastRow
and then clearing the rangeB2: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
CodePudding user response:
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.