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.


--
Regards,
Tom Ogilvy


"Mike" wrote in message
...
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

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.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
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.


--
Regards,
Tom Ogilvy


"Mike" wrote in message
...
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

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


In article ,
"Mike" wrote:

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

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


-----Original Message-----
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


In article ,
"Mike" wrote:

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
.

Since you have an algorithm in mind, you can easily adapt this sample code
from John Walkenbach's site to do this:

http://j-walk.com/ss/excel/tips/tip47.htm

--
Regards,
Tom Ogilvy


"Mike" wrote in message
...
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


-----Original Message-----
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


In article ,
"Mike" wrote:

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
.