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

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

"Ingmar Heinrich" wrote:

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

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


Replace @mailinator.com with @tiscali.co.uk


"Ingmar Heinrich" wrote in message
oups.com...
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

That worked fine, thanks!!

Ingmar

On Jun 19, 3:40 pm, "Sandy Mann" wrote:
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


Replace @mailinator.com with @tiscali.co.uk

"Ingmar Heinrich" wrote in message

oups.com...

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

You're Very welcome.

--
Regards,

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


Replace @mailinator.com with @tiscali.co.uk


"Ingmar Heinrich" wrote in message
oups.com...
That worked fine, thanks!!

Ingmar

On Jun 19, 3:40 pm, "Sandy Mann" wrote:
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


Replace @mailinator.com with @tiscali.co.uk

"Ingmar Heinrich" wrote in message

oups.com...

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