removing early duplicates

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
 
T

Tom Ogilvy

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.
 
J

JE McGimpsey

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
 
T

Tom Ogilvy

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.
 
M

Mike

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
 
Top