Last Duplicate Value in Column

How do you find the last duplicate value in a column?

Column B
BV
Vertex
ISO
Regional
BV
BV <<<<<<<<<<< Need to find this value
Overland
Vertex <<<<<<<<<Need to find this value
MUM
IUM

this would find the last anything filtered duplacates or whatever.


[vba]Sub Macro1()
Dim wk As Worksheet
Dim LastRow As Long
Dim LastCell As Range
Set wk = ActiveSheet
Application.ScreenUpdating = False
On Error GoTo x_it:
With wk
LastRow = Range("c" & Rows.Count).End(xlUp).Row
wk.Range("c2").Resize(LastRow - 1).ClearContents
wk.Columns(2).AutoFilter
wk.Range("$B$1").AutoFilter Field:=1, Criteria1:=Range("c1").Text


Set LastCell = .Columns(2).Find( _
What:="*", _
After:=[B1], _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious)
If Not LastCell Is Nothing Then
LastRow = LastCell.Row
End If
.Range("b" & LastRow).Offset(, 1) = "< last one here"
End With
wk.AutoFilterMode = False
Application.ScreenUpdating = True
Exit Sub
x_it:
Application.ScreenUpdating = True
MsgBox Err.Description
End Sub
[/vba]

Are you defining duplicate as any value that appears in a column more than once, or are you defining duplicate as any value that has an adjacent cell in the same column of the same value?

I'm defining duplicate as any value that appears in a column more than once. Thank you




I'm looking for a forumula that will place a value in Column F based on the value based on the the latest duplicate value in a column B

Since David has already posted code, I'll give you a non-code approach. Say you want to check out column A. Insert a blank column and in the top cell put =(COUNTIF($A$2:$A$21,A20)>1)*ROW()
Then just filter to your top one item. (Click top ten and you will be prompted for a number other than 10).

Why do you have 2, there is only one last duplicate value.

Good question:thumb Missed that detail :)

This array formula modification of Oorang's formula will return the row number of the one last duplicated value

=MAX(--(COUNTIF(B1:B1000,B1:B1000)>1)*ROW(B1:B1000))

=INDEX(B:B,rowNumber,1) will return the value itself.

(Array formulas are confirmed with Ctrl-Shift-Enter (Cmd-Return for Mac))