Re: Complicated Formulas

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 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
> >>
> >>
> >>
>
>
>
.