Special selecting problem

[Ingmar Heinrich]

Hi there!

I have a worksheet that has project numbers in column A, dates in
column B and events in column C, like:

A B C
1 2007-06-17 Whatever
2 2007-06-17 Some test
2 2007-06-18 Text galore
1 2007-06-18 You see?
1 2007-06-18 So what's up
3 2007-06-18 No idea.
2 2007-06-19 Help me!

The project numbers can be in an arbitrary order. Dates are rising or
staying the same when going down the sheet. This is some kind of log.

So what I need is a way to get the latest event for each project, like

1 2007-06-18 So what's up
2 2007-06-19 Help me!
3 2007-06-18 No idea.

I need formulae for the dates and the events. Any ideas anyone?

Cheers,
Ingmar

 

[Mike H]

Ingmar,

With a macro:-

Sub atomicparticles()
sp = " "
Dim project As Integer
project = InputBox("Enter project number")
lastrowcola = Range("A65536").End(xlUp).Row
For x = lastrowcola To 1 Step -1
Cells(x, 1).Select
If ActiveCell.Value = project Then
thedate = ActiveCell.Offset(0, 1).Value
thereason = ActiveCell.Offset(0, 2).Value
MsgBox (project & sp & thedate & sp & thereason)
End
End If
Next
End Sub

I've put the resiult in a text box but it could just as easily be written to
the worksheet.

Mike

 

[Sandy Mann]

With the project numbers in F1:F3 try:

Last date of project number in F1:
=SUMPRODUCT((MAX(($B$1:$B$7)*($A$1:$A$7=F1))))

Event of project in F1:
=INDEX($C$1:$C$7,SUMPRODUCT(MAX(($A$1:$A$7=F1)*ROW($A$1:$A$7))))

and copy down using the fill handle

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ingmar Heinrich]

That worked fine, thanks!!

Ingmar

With the project numbers in F1:F3 try:

Last date of project number in F1:
=SUMPRODUCT((MAX(($B$1:$B$7)*($A$1:$A$7=F1))))

Event of project in F1:
=INDEX($C$1:$C$7,SUMPRODUCT(MAX(($A$1:$A$7=F1)*ROW($A$1:$A$7))))

and copy down using the fill handle

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

You're Very welcome.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk