Hi thanks for this,

However my data is in a text file (1,000,000+ records!)
Once one works with a text file one needs a different approach correct?
The rc[-1] bit won't work with a text file. So how does one emulate this
code to loop through a text file?

I am not experienced with programming but I do try to write little bits of
code. This is the bit I struggle with:
Pol0003 Prev
Pol0003 Other
Pol0003 Other (This is the last record of Pol003 so record should be
allocated "Off" since there is no "Curr")

So I loop through the records above and now the code jumps to the next one
i.e
Pol0004 Prev

Now, because the Pol has changed (Pol0004), the previous record is an "Off"
record since there was no "Curr". But how does the programme move back to
the previous record to allocate the "Off" marker?

In my code the code does this:
Pol0004 Off

Thanks
Hilton



"Dave Peterson" wrote in message
...
I wouldn't bother with a macro.

I'd insert a new column (column C) and then at the last used row, put
this:

(11 was the last used row for my test data)
=IF(B11="curr",B11,"Off")

Then in B10, put this formula:
=IF(OR(B10={"curr","prev"}),B10,IF(A10=A11,B10,"Of f"))
and drag it up to row 1.

Then edit|copy followed by edit|paste special|values to convert the
formulas to
values.

In code:

Option Explicit
Sub testme01()
Dim wks As Worksheet
Dim LastRow As Long

Set wks = Worksheets("sheet1")
With wks
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Cells(LastRow, "C").FormulaR1C1 _
= "=IF(rc[-1]=""curr"",rc[-1],""Off"")"
With .Range("c1:c" & LastRow)
.FormulaR1C1 = "=IF(OR(rc[-1]={""curr"",""prev""})," _
&
"rc[-1],IF(rc[-2]=r[1]c[-2],rc[-1],""Off""))"
End With
With .Range("c:c")
.Value = .Value
End With
End With

End Sub

This assumes that column C is already empty and that the last item in
column B
should be Curr or Off.

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

--

Dave Peterson