Re: Complicated Formulas

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 news:54CA40AF-FE53-46E6-9CF9-E03F8FEF5E49@xxxxxxxxxxxxxxxx
> 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
>> (Hypnotic_Monkey_Scratcher)@discussions.microsoft.com> wrote in message
>> news:5C581A7C-1CFF-46D7-8F6A-0D7DF8C8B099@xxxxxxxxxxxxxxxx
>> > 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
>> > spread*** 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
>> >
>> > isitaaaandrew@xxxxxxxxxxx
>>
>>
>>


.