Hi

Got a bit of a complicated prob.....reguarly at work we transfer data from
one program to another. We have a 'matrix' to translate the data from one
format to another. Every week I have to update the matrix by running a
spreadsheet from the system and manually converting it into the other format
and then load it up ready for when we do the transfer. If I'm in a hurry or
whatever, mistakes are made in the matrix and it makes HUGE muckups in our
other system.

I'm looking at a way to automate it to counteract these human errors, but
its becoming a bit of an impossible task!! All the codes from one system are
in different formats and it requires me to know how they are translated,
trying to create a formula or whatever todo this is hard! There are never
any exact number of codes for each set each week, there can be none, so how
do I get excel to realise this?

I think Im going to have to use some VB todo this which I know almost zilch
of at the moment. Realise this is a long term problem so am willing to learn
it and see if I can solve it, but that takes time!

If anyone wants the challenge of trying to help me out, please email me and
I will send you the sample files so you can see what I'm going on about!!

Thx in advance

Andy

It's a bit of a tall order, expecting someone to jump in and say they'll
help with very little idea of what is required.

If you start with some examples of the sort of data you have and what you
need to "translate" it to, it may give folks an idea what's needed and offer
some suggestions.

--
Ian
--
"Andy (Hypnotic_Monkey_Scratcher)" <Andy
. com wrote in message
...
Hi

Got a bit of a complicated prob.....reguarly at work we transfer data from
one program to another. We have a 'matrix' to translate the data from one
format to another. Every week I have to update the matrix by running a
spreadsheet from the system and manually converting it into the other
format
and then load it up ready for when we do the transfer. If I'm in a hurry
or
whatever, mistakes are made in the matrix and it makes HUGE muckups in our
other system.

I'm looking at a way to automate it to counteract these human errors, but
its becoming a bit of an impossible task!! All the codes from one system
are
in different formats and it requires me to know how they are translated,
trying to create a formula or whatever todo this is hard! There are never
any exact number of codes for each set each week, there can be none, so
how
do I get excel to realise this?

I think Im going to have to use some VB todo this which I know almost
zilch
of at the moment. Realise this is a long term problem so am willing to
learn
it and see if I can solve it, but that takes time!

If anyone wants the challenge of trying to help me out, please email me
and
I will send you the sample files so you can see what I'm going on about!!

Thx in advance

Andy

Yere - sorry I do realise that. It was more of a panicked cry for help last
night when I posted that!

After speaking to a couple of guys about this, it looks like too big a task
to solve without professional conuslting to solve it. I've had one response
from a guy who made me think differently about how to work out how I do it,
but he still didn't solve the whole manual translation thing.

Basically, what I need todo is translate "L3882C-000-000-SA25" into 4
seperate codes for the second system "L3882,C000,000SA25,3600" or
"L0217F-1602V-350-000" into "L0217,01,1602V,9800". All the codes are
translated on a certain set of rules, depending on what division they are
for. E.g. the L3882 codes are translated different to L0217 codes. I was
looking for a way to create something that recognises what division the first
code is from, then apply a set of rules to translate the code into the 4
codes for the second system.

Try and think of it in words. I want to translate a word from english into
french. I know the english but not the french. I don't have a dictionary,
but the french word can be computed using a set of calculations. Then, add
into the fact that I want todo this for several different languages e.g.
english to russian, english to german and each language uses a different
method of finding the correct word. E.g. english = hello & french =
hello1112. The formula for english to french is just add 1112 on the end.
But its a lot more complicated than just that.......

Kind of hard to understand without seeing the files and having a prior
understanding of the dept.

Thanks for replying, but I think now I'm just going to have to continue todo
it manually.

Cheers,

Andy


"Ian" wrote:

It's a bit of a tall order, expecting someone to jump in and say they'll
help with very little idea of what is required.

If you start with some examples of the sort of data you have and what you
need to "translate" it to, it may give folks an idea what's needed and offer
some suggestions.

--
Ian
--
"Andy (Hypnotic_Monkey_Scratcher)" <Andy
. com wrote in message
...
Hi

Got a bit of a complicated prob.....reguarly at work we transfer data from
one program to another. We have a 'matrix' to translate the data from one
format to another. Every week I have to update the matrix by running a
spreadsheet from the system and manually converting it into the other
format
and then load it up ready for when we do the transfer. If I'm in a hurry
or
whatever, mistakes are made in the matrix and it makes HUGE muckups in our
other system.

I'm looking at a way to automate it to counteract these human errors, but
its becoming a bit of an impossible task!! All the codes from one system
are
in different formats and it requires me to know how they are translated,
trying to create a formula or whatever todo this is hard! There are never
any exact number of codes for each set each week, there can be none, so
how
do I get excel to realise this?

I think Im going to have to use some VB todo this which I know almost
zilch
of at the moment. Realise this is a long term problem so am willing to
learn
it and see if I can solve it, but that takes time!

If anyone wants the challenge of trying to help me out, please email me
and
I will send you the sample files so you can see what I'm going on about!!

Thx in advance

Andy

This macro will convert the 2 examples you gave, taking data in column A and
putting the converted data in column B. I'm assuming in the first case that
the C000 is the C from the first part and 000 from the second, and that
000SA25 is the 3rd & 4th parts combined. I wasn'r certain because 000
appears twice.

Sub Convert_matrix()
c = 1 ' Column number for source data (A=1, B=2 etc.)
For r = 1 To 7 ' Row range for source data
Select Case Left(Cells(r, c), 6)
Case "L3882C"
Cells(r, c + 1).Value = Left(Cells(r, c), 5) & "," & Mid(Cells(r, c), 6, 1)
& Mid(Cells(r, c), 8, 3) & "," & Mid(Cells(r, c), 12, 3) & Right(Cells(r,
c), 4) & ",3600"
Case "L0217F"
Cells(r, c + 1).Value = Left(Cells(r, c), 5) & ",01," & Mid(Cells(r, c), 8,
4) & ",9800"
End Select
Next
End Sub

--
Ian
--
"Andy (Hypnotic_Monkey_Scratcher)" <isitaaaandrew"at"hotmail"dot"com wrote
in message ...
Yere - sorry I do realise that. It was more of a panicked cry for help
last
night when I posted that!

After speaking to a couple of guys about this, it looks like too big a
task
to solve without professional conuslting to solve it. I've had one
response
from a guy who made me think differently about how to work out how I do
it,
but he still didn't solve the whole manual translation thing.

Basically, what I need todo is translate "L3882C-000-000-SA25" into 4
seperate codes for the second system "L3882,C000,000SA25,3600" or
"L0217F-1602V-350-000" into "L0217,01,1602V,9800". All the codes are
translated on a certain set of rules, depending on what division they are
for. E.g. the L3882 codes are translated different to L0217 codes. I was
looking for a way to create something that recognises what division the
first
code is from, then apply a set of rules to translate the code into the 4
codes for the second system.

Try and think of it in words. I want to translate a word from english
into
french. I know the english but not the french. I don't have a
dictionary,
but the french word can be computed using a set of calculations. Then,
add
into the fact that I want todo this for several different languages e.g.
english to russian, english to german and each language uses a different
method of finding the correct word. E.g. english = hello & french =
hello1112. The formula for english to french is just add 1112 on the end.
But its a lot more complicated than just that.......

Kind of hard to understand without seeing the files and having a prior
understanding of the dept.

Thanks for replying, but I think now I'm just going to have to continue
todo
it manually.

Cheers,

Andy


"Ian" wrote:

It's a bit of a tall order, expecting someone to jump in and say they'll
help with very little idea of what is required.

If you start with some examples of the sort of data you have and what you
need to "translate" it to, it may give folks an idea what's needed and
offer
some suggestions.

--
Ian
--
"Andy (Hypnotic_Monkey_Scratcher)" <Andy
. com wrote in message
...
Hi

Got a bit of a complicated prob.....reguarly at work we transfer data
from
one program to another. We have a 'matrix' to translate the data from
one
format to another. Every week I have to update the matrix by running a
spreadsheet from the system and manually converting it into the other
format
and then load it up ready for when we do the transfer. If I'm in a
hurry
or
whatever, mistakes are made in the matrix and it makes HUGE muckups in
our
other system.

I'm looking at a way to automate it to counteract these human errors,
but
its becoming a bit of an impossible task!! All the codes from one
system
are
in different formats and it requires me to know how they are
translated,
trying to create a formula or whatever todo this is hard! There are
never
any exact number of codes for each set each week, there can be none, so
how
do I get excel to realise this?

I think Im going to have to use some VB todo this which I know almost
zilch
of at the moment. Realise this is a long term problem so am willing to
learn
it and see if I can solve it, but that takes time!

If anyone wants the challenge of trying to help me out, please email me
and
I will send you the sample files so you can see what I'm going on
about!!

Thx in advance

Andy

Ian - you are a star! That is brilliant!

All I have todo now is create different 'Cases' for the different divisions
(unfortunately there are more than just two!)! Might take me a while to work
through them, but that is a definite start in the right direction!

I owe you one!

Thanks,

Andy



"Ian" wrote:

This macro will convert the 2 examples you gave, taking data in column A and
putting the converted data in column B. I'm assuming in the first case that
the C000 is the C from the first part and 000 from the second, and that
000SA25 is the 3rd & 4th parts combined. I wasn'r certain because 000
appears twice.

Sub Convert_matrix()
c = 1 ' Column number for source data (A=1, B=2 etc.)
For r = 1 To 7 ' Row range for source data
Select Case Left(Cells(r, c), 6)
Case "L3882C"
Cells(r, c + 1).Value = Left(Cells(r, c), 5) & "," & Mid(Cells(r, c), 6, 1)
& Mid(Cells(r, c), 8, 3) & "," & Mid(Cells(r, c), 12, 3) & Right(Cells(r,
c), 4) & ",3600"
Case "L0217F"
Cells(r, c + 1).Value = Left(Cells(r, c), 5) & ",01," & Mid(Cells(r, c), 8,
4) & ",9800"
End Select
Next
End Sub

--
Ian
--
"Andy (Hypnotic_Monkey_Scratcher)" <isitaaaandrew"at"hotmail"dot"com wrote
in message ...
Yere - sorry I do realise that. It was more of a panicked cry for help
last
night when I posted that!

After speaking to a couple of guys about this, it looks like too big a
task
to solve without professional conuslting to solve it. I've had one
response
from a guy who made me think differently about how to work out how I do
it,
but he still didn't solve the whole manual translation thing.

Basically, what I need todo is translate "L3882C-000-000-SA25" into 4
seperate codes for the second system "L3882,C000,000SA25,3600" or
"L0217F-1602V-350-000" into "L0217,01,1602V,9800". All the codes are
translated on a certain set of rules, depending on what division they are
for. E.g. the L3882 codes are translated different to L0217 codes. I was
looking for a way to create something that recognises what division the
first
code is from, then apply a set of rules to translate the code into the 4
codes for the second system.

Try and think of it in words. I want to translate a word from english
into
french. I know the english but not the french. I don't have a
dictionary,
but the french word can be computed using a set of calculations. Then,
add
into the fact that I want todo this for several different languages e.g.
english to russian, english to german and each language uses a different
method of finding the correct word. E.g. english = hello & french =
hello1112. The formula for english to french is just add 1112 on the end.
But its a lot more complicated than just that.......

Kind of hard to understand without seeing the files and having a prior
understanding of the dept.

Thanks for replying, but I think now I'm just going to have to continue
todo
it manually.

Cheers,

Andy


"Ian" wrote:

It's a bit of a tall order, expecting someone to jump in and say they'll
help with very little idea of what is required.

If you start with some examples of the sort of data you have and what you
need to "translate" it to, it may give folks an idea what's needed and
offer
some suggestions.

--
Ian
--
"Andy (Hypnotic_Monkey_Scratcher)" <Andy
. com wrote in message
...
Hi

Got a bit of a complicated prob.....reguarly at work we transfer data
from
one program to another. We have a 'matrix' to translate the data from
one
format to another. Every week I have to update the matrix by running a
spreadsheet from the system and manually converting it into the other
format
and then load it up ready for when we do the transfer. If I'm in a
hurry
or
whatever, mistakes are made in the matrix and it makes HUGE muckups in
our
other system.

I'm looking at a way to automate it to counteract these human errors,
but
its becoming a bit of an impossible task!! All the codes from one
system
are
in different formats and it requires me to know how they are
translated,
trying to create a formula or whatever todo this is hard! There are
never
any exact number of codes for each set each week, there can be none, so
how
do I get excel to realise this?

I think Im going to have to use some VB todo this which I know almost
zilch
of at the moment. Realise this is a long term problem so am willing to
learn
it and see if I can solve it, but that takes time!

If anyone wants the challenge of trying to help me out, please email me
and
I will send you the sample files so you can see what I'm going on
about!!

Thx in advance

Andy

I assumed there world be more than 2 cases. I don't know if there's a limit
to the number of cases. Excel help does not specify. If there are a lot of
cases, it may be worth creating the required number (just the Case "xyz"
bit) with any old values, just to test it before you go to the trouble of
working out the formulae.

BTW, the lines have been wrapped, there was only one formula line for each
case. If you want to keep the lines short (so they stay on screen) you can
enter an underscore "_" at the end of one part of the formula and continue
on the next line.

--
Ian
--
"Andy (Hypnotic_Monkey_Scratcher)" <isitaaaandrew"at"hotmail"dot"com wrote
in message ...
Ian - you are a star! That is brilliant!

All I have todo now is create different 'Cases' for the different
divisions
(unfortunately there are more than just two!)! Might take me a while to
work
through them, but that is a definite start in the right direction!

I owe you one!

Thanks,

Andy



"Ian" wrote:

This macro will convert the 2 examples you gave, taking data in column A
and
putting the converted data in column B. I'm assuming in the first case
that
the C000 is the C from the first part and 000 from the second, and that
000SA25 is the 3rd & 4th parts combined. I wasn'r certain because 000
appears twice.

Sub Convert_matrix()
c = 1 ' Column number for source data (A=1, B=2 etc.)
For r = 1 To 7 ' Row range for source data
Select Case Left(Cells(r, c), 6)
Case "L3882C"
Cells(r, c + 1).Value = Left(Cells(r, c), 5) & "," & Mid(Cells(r, c), 6,
1)
& Mid(Cells(r, c), 8, 3) & "," & Mid(Cells(r, c), 12, 3) & Right(Cells(r,
c), 4) & ",3600"
Case "L0217F"
Cells(r, c + 1).Value = Left(Cells(r, c), 5) & ",01," & Mid(Cells(r, c),
8,
4) & ",9800"
End Select
Next
End Sub

--
Ian
--
"Andy (Hypnotic_Monkey_Scratcher)" <isitaaaandrew"at"hotmail"dot"com
wrote
in message ...
Yere - sorry I do realise that. It was more of a panicked cry for help
last
night when I posted that!

After speaking to a couple of guys about this, it looks like too big a
task
to solve without professional conuslting to solve it. I've had one
response
from a guy who made me think differently about how to work out how I do
it,
but he still didn't solve the whole manual translation thing.

Basically, what I need todo is translate "L3882C-000-000-SA25" into 4
seperate codes for the second system "L3882,C000,000SA25,3600" or
"L0217F-1602V-350-000" into "L0217,01,1602V,9800". All the codes are
translated on a certain set of rules, depending on what division they
are
for. E.g. the L3882 codes are translated different to L0217 codes. I
was
looking for a way to create something that recognises what division the
first
code is from, then apply a set of rules to translate the code into the
4
codes for the second system.

Try and think of it in words. I want to translate a word from english
into
french. I know the english but not the french. I don't have a
dictionary,
but the french word can be computed using a set of calculations. Then,
add
into the fact that I want todo this for several different languages
e.g.
english to russian, english to german and each language uses a
different
method of finding the correct word. E.g. english = hello & french =
hello1112. The formula for english to french is just add 1112 on the
end.
But its a lot more complicated than just that.......

Kind of hard to understand without seeing the files and having a prior
understanding of the dept.

Thanks for replying, but I think now I'm just going to have to continue
todo
it manually.

Cheers,

Andy


"Ian" wrote:

It's a bit of a tall order, expecting someone to jump in and say
they'll
help with very little idea of what is required.

If you start with some examples of the sort of data you have and what
you
need to "translate" it to, it may give folks an idea what's needed and
offer
some suggestions.

--
Ian
--
"Andy (Hypnotic_Monkey_Scratcher)" <Andy
. com wrote in
message
...
Hi

Got a bit of a complicated prob.....reguarly at work we transfer
data
from
one program to another. We have a 'matrix' to translate the data
from
one
format to another. Every week I have to update the matrix by
running a
spreadsheet from the system and manually converting it into the
other
format
and then load it up ready for when we do the transfer. If I'm in a
hurry
or
whatever, mistakes are made in the matrix and it makes HUGE muckups
in
our
other system.

I'm looking at a way to automate it to counteract these human
errors,
but
its becoming a bit of an impossible task!! All the codes from one
system
are
in different formats and it requires me to know how they are
translated,
trying to create a formula or whatever todo this is hard! There are
never
any exact number of codes for each set each week, there can be none,
so
how
do I get excel to realise this?

I think Im going to have to use some VB todo this which I know
almost
zilch
of at the moment. Realise this is a long term problem so am willing
to
learn
it and see if I can solve it, but that takes time!

If anyone wants the challenge of trying to help me out, please email
me
and
I will send you the sample files so you can see what I'm going on
about!!

Thx in advance

Andy

Thanx, i realised that because excel said there was an error in the formula
so I just made it all go onto one line for now.

I added a few duff ones and it worked ok, will see roughly how many cases I
need when I get back to work on tuesday (not sure how many divisions there
are, can think of about 10 but will run a report on tuesday to make sure) and
add those in to see how it works. I guess excel shouldn't have a limit but
it might just take a while to work through them. I'll see how it goes and
let you know.

Thanks mate now I'm off down to the pub! I'd buy you a pint in return but
Im guessing you don't live next door!

Cheers again,

Andy

"Ian" wrote:

I assumed there world be more than 2 cases. I don't know if there's a limit
to the number of cases. Excel help does not specify. If there are a lot of
cases, it may be worth creating the required number (just the Case "xyz"
bit) with any old values, just to test it before you go to the trouble of
working out the formulae.

BTW, the lines have been wrapped, there was only one formula line for each
case. If you want to keep the lines short (so they stay on screen) you can
enter an underscore "_" at the end of one part of the formula and continue
on the next line.

--
Ian
--

"Andy (Hypnotic_Monkey_Scratcher)" <isitaaaandrew"at"hotmail"dot"com wrote
in message ...
I added a few duff ones and it worked ok, will see roughly how many cases
I
need when I get back to work on tuesday (not sure how many divisions there
are, can think of about 10 but will run a report on tuesday to make sure)
and
add those in to see how it works. I guess excel shouldn't have a limit
but
it might just take a while to work through them. I'll see how it goes and
let you know.

I was thinking of a limit inthe order of eg 255. 10 to 20 will be no
problem.

Thanks mate now I'm off down to the pub! I'd buy you a pint in return but
Im guessing you don't live next door!


You never know. Something tells me you're in the UK so post the address of
the pub and I'll see if it'll be worth the drive :-)

--
Ian
--