removing early duplicates

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Can anyone help me with a UDF to remove early duplicates
from a list?

Example:
Input:
A
B
A
C
B

Output:
A
C
B

Thanks,
Mike
 
Assume you data is in A1 going down column A.

in B1 put in a formula

=if(Countif($A$1:A1,A1)=1,"Keep","Delete")

then drag fill this down the column.

select A1 and then do Data=>Filter=>Autofilter

In the dropdown in B1, select Delete.

Then highlight all the rows below Row A and do Edit=>Delete.

Now do Data=>Filter=>Autofilter to turn off the autofilter.

Only the unique list should remain.

If this isn't what you want, post back with what you mean by UDF. Generally
a UDF refers to a vba custom function that is used in a worksheet as a
formula - but formulas can't change existing cells - they can't delete data
inplace.
 
One, inefficient, way:

Public Function RemoveEarlyDups(ByRef rIn As Range) As Variant
Dim vTemp As Variant
Dim vOut As Variant
Dim vUnique As Variant
Dim i As Long
Dim j As Long
Dim k As Long

If rIn.Columns.Count > 1 Then
RemoveEarlyDups = CVErr(xlErrRef)
Else
vTemp = rIn.Value
ReDim vUnique(1 To UBound(vTemp, 1))
k = UBound(vUnique)
For i = UBound(vTemp, 1) To 2 Step -1
If Not IsEmpty(vTemp(i, 1)) Then
vUnique(k) = vTemp(i, 1)
k = k - 1
For j = i - 1 To 1 Step -1
If vTemp(j, 1) = vTemp(i, 1) Then _
vTemp(j, 1) = Empty
Next j
End If
Next i
j = 1
ReDim vOut(k + 1 To UBound(vUnique))
For i = k + 1 To UBound(vUnique)
vOut(i) = vUnique(i)
Next i
RemoveEarlyDups = Application.Transpose(vOut)
End If
End Function


this returns an array of unique values
 
If you want to keep the last row that contains the value rather than the
first ("removing early duplicates") change the formula to

=if(Countif(A1:$A$2000,A1)=1,"Keep","Delete")

Where $A$2000 should refer to your last data cell or beyond.
 
This is great! Do you have any idea how to implement
this in a way like Microsoft's inherent "Advanced Filter"
function?

One way I think this can be more efficient is:
1. Insert counter
2. Sort list be entries by (A) entry (B) reverse counter
3. Remove duplicates besides the first
4. Resort by counter
5. Post into desired cells

Thanks!
Mike
 
Back
Top