Complicated Formula/Code needed

Put this in Worksheet Functions, probably better off in here
though.......sorry!!

**********************************************************

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

[email protected]

When you're talking about mapping "formats" do you mean the physical
layout in terms of columns and rows, or mapping between different sets
of values?

Tim.


"Andy (Hypnotic_Monkey_Scratcher)"
<[email protected]> wrote in
message news:[email protected]...
> Put this in Worksheet Functions, probably better off in here
> though.......sorry!!
>
> **********************************************************
>
> 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
>
> [email protected]
>

Between different sets of values.

e.g. in System 1 the code is "L3882C-000-000-SA25" and in System 2 is
translated into 4 different parts (seperated by commas)
"L3882,C000,000SA25,3600".

Sorry, realise now using the word 'format' was confusing.

Andy




"Tim Williams" wrote:

> When you're talking about mapping "formats" do you mean the physical
> layout in terms of columns and rows, or mapping between different sets
> of values?
>
> Tim.
>
>
> "Andy (Hypnotic_Monkey_Scratcher)"
> <[email protected]> wrote in
> message news:[email protected]...
> > Put this in Worksheet Functions, probably better off in here
> > though.......sorry!!
> >
> > **********************************************************
> >
> > 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
> >
> > [email protected]
> >
>
>
>

If you want to send them
[email protected]

--
Regards,
Tom Ogilvy

"Andy (Hypnotic_Monkey_Scratcher)"
<[email protected]> wrote in message
news:[email protected]...
> Put this in Worksheet Functions, probably better off in here
> though.......sorry!!
>
> **********************************************************
>
> 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
>
> [email protected]
>

Hi Tom

I have sent them through to you, didn't register in my hotmail sent items
and the hotmail window has crashed now a few times so if you didn't receive
it properly let me know and I will resend from my BT account.

Cheers,

Andy

"Tom Ogilvy" wrote:

> If you want to send them
> [email protected]
>
> --
> Regards,
> Tom Ogilvy
>

Assume this is you:

"adhocrunsubmitrep2.xls is the sample file . . . "

Will take a look.

--
Regards,
Tom Ogilvy

"Andy (Hypnotic_Monkey_Scratcher)" <isitaaaandrew"at"hotmail"dot"com> wrote
in message news:[email protected]...
> Hi Tom
>
> I have sent them through to you, didn't register in my hotmail sent items
> and the hotmail window has crashed now a few times so if you didn't
receive
> it properly let me know and I will resend from my BT account.
>
> Cheers,
>
> Andy
>
> "Tom Ogilvy" wrote:
>
> > If you want to send them
> > [email protected]
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
>