Last Duplicate Value in Column

[LOSS1574]

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

[david000]

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]

[Oorang]

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?

[LOSS1574]

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

[Oorang]

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

[Bob Phillips]

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

[Oorang]

Good question Missed that detail

[mikerickson]

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))