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

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

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

Anyone can help?

"Hilton" wrote in message
...
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

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

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