Need help with coding

[Steve]

Hope some one can help, first need to paste the following
text into a text file, upto the dotted line -


Original two lines below

Name
cc a/c
sub
DR CR
bla
bla1
bla2




GECC LLAS INTERFACE 31-MAR-20049XXX01
6010 010100001
2999
90721722
90721722C0000000000000003103Susp
Rfnd/ 000000000
GECC LLAS INTERFACE 31-MAR-20049XXX01
6010 010100001
2999
334830
334830C0002047360000001612Gross
Paym 31IN77 000000000



THis is what i would need additionally produced in a
seperate text file


GECC LLAS INTERFACE 31-MAR-20049J3501
6010 010100001
2999
90721722
90721722C0000000000000003103Susp
Rfnd/ 000000000
GECC LLAS INTERFACE 31-MAR-20049J3501
6010 010100001
2999
334830
334830C0002047360000001612Gross
Paym 31IN77 000000000

GECC LLAS INTERFACE 31-MAR-20049XXX01
6010 010100001
2999
334830
334830D0002047360000001612Gross
Paym 31IN77 000000000
GECC LLAS INTERFACE 31-MAR-20049XXX01
6010 010100001
2999
90721722
90721722D0000000000000003103Susp
Rfnd/ 000000000




------------------------------------------------------


If you view the text above within a text file you will see
Original data made up of two lines. I have put some
headers in to try and make it a little easier. These
headers will not be there though when i have to run the
coding.

The two lines in 'original two lines below' represent 2
credit entries. (look right accross the page) these are
identified by a C in the text in the 'bla' column. If they
were debits there would be a D in the code within
the 'bla' column.

Firstly, in any row where there is an occurance of all
three of the following in the original 'two lines below
data'-

Name - 9XXX01
a/c - 010100001
sub - 2999

I would need to create two extra lines of data. These
lines will need to be dumped into a seperate text file.


The first line of the two lines needed to be dumped will
be identical to the line where there are these three
occurances except for the following - text being replaced
within the Name

GECC LLAS INTERFACE 31-MAR-20049XXX01 replaced with GECC
LLAS INTERFACE 31-MAR-20049J3501

That is all for the first line. easy?


The second line a little more difficult -

It needs to be identical to the original line changing
only the following fields -

Dr - if anything in here on the original line put the
amount into Cr on new line
Cr - if anything in here on the original line put the
amount into Dr on new line

Also with the data in column 'bla' if there is an
occurance of C or D in the code - ie:
90721722C0000000000000003103Susp Rfnd/
This will have to be replaced. The C with D or if D with C.

This is going to be a reversing entry

Ideally if we do above for the two 'original two lines
below entry' it will create the four entries shown 'This
is what i would need additionaly produced in a seperate
text file''

I hope this makes sense and you can help me

 

[Harlan Grove]

Hope some one can help, first need to paste the following
text into a text file, upto the dotted line - ....
------------------------------------------------------

....

Dashed line perhaps.

Firstly, in any row where there is an occurance of all
three of the following in the original 'two lines below
data'-

Name - 9XXX01
a/c - 010100001
sub - 2999

It'd appear more precise to say that the name field *ends* with 9XXX01.
Would this bit be at the end of the Name field or could it appear anywhere
in that field? If only at the end,

=COUNTIF(NameField,"*"&"9XXX01")

would be >0 if there were a match, =0 otherwise, where NameField would be a
reference to the name field for some record within a list/range of such
records. Much easier to match the a/c and sub fields. Combining them
requires only

=AND(COUNTIF(NameField,"*"&"9XXX01"),ACField="010100001",SubField=2999)

I would need to create two extra lines of data. These
lines will need to be dumped into a seperate text file.

The first line of the two lines needed to be dumped will
be identical to the line where there are these three
occurances except for the following - text being replaced
within the Name

GECC LLAS INTERFACE 31-MAR-20049XXX01

replaced with

GECC LLAS INTERFACE 31-MAR-20049J3501

That is all for the first line. easy?

Yup. =SUBSTITUTE(NameField,"9XXX01","9J3501")

The second line a little more difficult -

It needs to be identical to the original line changing
only the following fields -

Dr - if anything in here on the original line put the
amount into Cr on new line
Cr - if anything in here on the original line put the
amount into Dr on new line

DR field for some record:
=IF(TRIM(CRFieldOtherRecord)<>"",CRFieldOtherRecord,"")

CR field for this same record:

Also with the data in column 'bla' if there is an
occurance of C or D in the code - ie:
90721722C0000000000000003103Susp Rfnd/
This will have to be replaced. The C with D or if D with C.

Only a little more challenging. Does the bla field begin at the initial 9's
character position in the record? If so,

bla field in second output file:
=LEFT(BlaField,8)&LOOKUP(MID(BlaField,9,1),{"C";"D"},{"D";"C"})
&MID(BlahField,10,BlaFieldWidth-9)

I hope this makes sense and you can help me

The formulas above would do most of the work. That said, your 'fields'
appear to have multiple subfields. Whether you're dealing with fixed width
fields in single cells or your records have different 'superfields' in
different columns, Excel (and no other spreadsheet) is far from ideal for
this sort of thing. This is more of a database task. Do you have no database
system to use to do this? Scripting languages would be much better suited to
this sort of thing than Excel. Why are you trying to drive screws with a
jigsaw?