Re: Complicated Formulas

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 news:09F519C0-E45E-4E85-B019-DA402C4184C0@xxxxxxxxxxxxxxxx
> 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
>> >>
>> >>
>> >>
>>
>>
>>


.