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
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?
Cordially,
Aaron
Keep Our Board Clean!
- Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
- Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.
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
Last edited by LOSS1574; 06-12-2008 at 11:36 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).
Cordially,
Aaron
Keep Our Board Clean!
- Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
- Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.
Why do you have 2, there is only one last duplicate value.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Good question Missed that detail
Cordially,
Aaron
Keep Our Board Clean!
- Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
- Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.
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))