PDA

View Full Version : Last Duplicate Value in Column



LOSS1574
06-12-2008, 08:36 AM
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
06-12-2008, 10:16 AM
this would find the last anything filtered duplacates or whatever.


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

Oorang
06-12-2008, 10:18 AM
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
06-12-2008, 10:53 AM
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
06-12-2008, 11:55 AM
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
06-12-2008, 12:11 PM
Why do you have 2, there is only one last duplicate value.

Oorang
06-12-2008, 02:10 PM
Good question:thumb Missed that detail :)

mikerickson
06-12-2008, 11:32 PM
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))