last duplicate value

R

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!
 
J

Jim Jackson

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
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top