return data for last record in sequence by row

HI,

I have attached a sample worksheet.
See column A contains either "group 1" or "group 2"
Column B is the unique identifier for the client.

What i am looking for is a function in column C that will look down column B and for each "new" identifier (as seen in column C that tells us if the client record is new or if it is part of a sequence) tells us what Group the record belongs to in the last record in that sequence.
That is probably explained very badly, so here is an example:

For identifier 914703 (range A22:D24) we see 3 records:
Row 22 is the first record in the sequence so is marked as "new" in column C.
The last record in that sequence is row 24, at which time the record type is Group 2.
So in column E we see "group 1 for the start type and "group 2" for the end type.

Similarly for 780886 if column C says "new and last" then column E would be populated with Group 1 and column F with group 2 since it is the only record in that sequence.

Hope this can be achieved.
I have tried a number of offset and multiple column functions but cant get it right.
all help much appreciated
thanks
Luce

Not sure if I understood correctly, but in E2 try:

Please Login or Register  to view this content.

copied down

and in F2:

Please Login or Register  to view this content.

copied down.

Is that it?

Great!

Can you please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


Also, please update your profile to indicate Office Version and your location. This info makes it easier for responders to cater solutions to your version and locale.

Thanks.