last duplicate value

[rachitm]

Hi...

How could I find the last duplicate value the following way:


IF this is the data,


A B


1: day1 Jan
2: day1 Feb
3: day1 Mar
4: day2 Apr
5: day2 May
6: day3 June
7: day3 July
8: day5 Aug
9: day5 Sep


How could I display the last value of each type and the respective
column B data (in a textbox):

A B

day1 Mar
day2 May
day3 July
day5 Sep


Thanks!

 

[Guest]

Sub Find Dupes

Range("A1").Activate
Do
If Activecell = Activecell.Offset(1,0) Then
Activecell.Offset(1,0).Activate
Else
MsgBox("The Last duplicate is " & Activecell.Offset(0,1))
End If
ActiveCell.Offset(1,0).Activate
Loop until Activecell = ""
End Sub

 

[Bob Phillips]

=INDEX($B$1:$B$20,MAX(IF($A$1:$A$20="day1",ROW($A$1:$A$20))))

this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)