UPC Equation

[PneumaGT]

Yeah the first half was dead on Niek, Thanks! The "C" equation did not
work however.

I definietly got an answer for multiple fields. Now i just need to
figure out a way (if possible) to conjunct that together so that when i
enter an 11 digit number in A1 the corresponding 12 digit number in B1
appears. I would allow for more fields but her employer will be looking
at this and is none to bright. If we have a middle step on the exel
sheet, it is likely to cause more problems than solve.

By the way, thanks for all the help so far, it has really been
appreciated.

Solutions?

 

[BenjieLop]

Yeah the first half was dead on Niek, Thanks! The "C" equation did not
work however.

I definietly got an answer for multiple fields. Now i just need to
figure out a way (if possible) to conjunct that together so that when i
enter an 11 digit number in A1 the corresponding 12 digit number in B1
appears. I would allow for more fields but her employer will be looking
at this and is none to bright. If we have a middle step on the exel
sheet, it is likely to cause more problems than solve.

By the way, thanks for all the help so far, it has really been
appreciated.

Solutions?

It maybe possible to do what you want to do (enter the 11 digit number
in Cell A1 and the corresponding code appears in Cell B1). All you have
to do is combine all the "step formulas" that were given to you into one
super formula. You have all the formulas at your disposal so all you
have to do is find a way to combine them and make the super formula
work.

I am not really a fan of doing a super formula especially involving
long formulas. I would rather do it step by step.

Here is a suggestion though:

1. Enter the 11-digit number in Column A (starting in Cell A1)
2. Enter all the "step formulas" in Columns C,D,E,F (starting at row
1)
3. Enter in Column B the formula =E1+F1
4. Highlight Columns C,D,E,F and hide these columns

Columns C to F are now hidden and so when the boss looks at the
worksheet, all that can be seen are entries in 2 columns (Column A &
Column B). Since you mentioned that her employer is not too bright,
he/she may not even notice that columns C, D, E and F are hidden!!!

Again, this is only my humble suggestion ... there may be better and
more elegant from the real experts in this NG.

 

[RagDyer]

Do you really have to do this in a *single* cell?
Try this in B1:

=A1&(--RIGHT(SUMPRODUCT(--MID(A1,{1,3,5,7,9,11},1))*3+SUMPRODUCT(--MID(A1,{2
,4,6,8,10},1)))<>0)*(10-MOD(SUMPRODUCT(--MID(A1,{1,3,5,7,9,11},1))*3+SUMPROD
UCT(--MID(A1,{2,4,6,8,10},1)),10))

If you would accept the use of 2 cells, one in an "out-of-the-way" column,
such as Z1, look at this:

Enter this in Z1:
=SUMPRODUCT(--MID(A1,{1,3,5,7,9,11},1))*3+SUMPRODUCT(--MID(A1,{2,4,6,8,10},1
))

Enter this in B1:
=A1&(--RIGHT(Z1)<>0)*10-MOD(Z1,10)

It sure makes it a lot shorter and less complicated!
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Yeah the first half was dead on Niek, Thanks! The "C" equation did not
work however.

I definietly got an answer for multiple fields. Now i just need to
figure out a way (if possible) to conjunct that together so that when i
enter an 11 digit number in A1 the corresponding 12 digit number in B1
appears. I would allow for more fields but her employer will be looking
at this and is none to bright. If we have a middle step on the exel
sheet, it is likely to cause more problems than solve.

By the way, thanks for all the help so far, it has really been
appreciated.

Solutions?