Yes whenever the last record in a pol set is not "curr" then allocate "off".
I do require VB code for this since I am dealing with 1000000+ recs!

"veryeavy" wrote in message
...
Not sure I have totally "got" this.

I could make a generalisation about cases like this - if you can
absolutely
definitely 100% define what you want to do then someone will be able to
help
you do it.

At the risk of asking a leading question ...

Are you saying that if your file goes:

Pol0003 Prev
Pol0003 Other
Pol0003 Other

You want to replace the Last Other with Off?

And if so are we talking about the data having been imported into Excel
and
being in 2 separate columns?

If so it could be as easy as putting a formula in a third column where the
logic goes:

=if(and(a2<a1,b1<"Curr"),"Off",b1)

HTH,

Cheers,

Matt



"Hilton" wrote:

I have a working programme which aggregates records using a predefined
key,
but I need to add the following functionality:

I have a text file (comma separated) which is of the following structure
(goes to1000000 records):
Pol0001 Prev
Pol0001 Other
Pol0001 Other
Pol0001 Curr
Pol0002 Prev
Pol0002 Curr
Pol0003 Prev
Pol0003 Other
Pol0003 Other
Pol0004 Prev
Pol0004 Curr

Each Pol starts with a PREV and end with a CURR. However when there is
no
CURR (Pol0003) the programme should interpret this as an "OFF" record
and
show the following

Pol0003 Prev
Pol0003 Other
Pol0003 Off

Then the programme will do it's usual aggregation including the newly
identified off records too.

I have tried some code but I am getting very confused as to how to do
this.
I'm not aligning the "off" record where it should be.
I need some expert help. A simple example would do.

Can someone please help.

Thanks
Hilton