How to combine these 11 cells into one cell for upc formula?

[Rocky2889]

Currently I am using this formula for UPC code generating. But the problem is I have all digit 11 cells. Is there anyway to combine into cell. Here is the formula.

=MOD(10-(MOD(((A1+C1+E1+G1+I1+K1)*3)+(B1+D1+F1+H1+J1),10)),10)

Thanks

Rocky

 

[sweater_vests_rock]

Rocky.

What data is in cells A1 -> K1? What is your MOD formula trying to do?

Ben.

 

[Rocky2889]

What data is in cells A1 -> K1? What is your MOD formula trying to do?

The data is just like this

a1 b1 c1 d1 e1 f1 g1 h1 i1 j1 k1
2 6 8 2 6 8 1 2 3 4 5

All the cells data is number. the result of the formula is 26826812345 plus a checksum digit.

Thanks

Rocky

 

[sweater_vests_rock]

Rocky.

I'm sorry I still don't understand. The formula only spits out a single digit - in the example provided, this is 3.

If you want to combine your individual cell entries into a single cell, use

=A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1

This gives you a text value - for the inputs you provided, 26826812345.

If you want the Mod formula output included, simply add the cell address into the formula above. Or, you could say

=(A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1)&MOD(10-(MOD(((A1+C1+E1+G1+I1+K1)*3)+(B1+D1+F1+H1+J1),10)),10)

If none of this is what you want to do, I guess try me again?

Sorry to be dense. Ben.

 

[Rocky2889]

Ben,

I try to enter these numbers 26826812345 into one cell. Instead of entering #2 in cell a1, #6 in cell b1 and #8 in cell c1 and so on.

Saving from type each cell number.

Your result of 3 is correct.

Thanks

Rocky

 

[sweater_vests_rock]

Rocky.

Ok, I'm finally with you, and I think I have what you want

1) Enter in cell A1: 26826812345
2) Enter in cells B1->J1: =MID(A1, X, 1), where X = 1 for ColB, 2 for ColC, etc.
3) Enter in Cell M1: Your Mod Formula =MOD(10-(MOD(((B1+D1+F1+H1+J1+L1)*3)+(C1+E1+G1+I1+K1),10)),10)

Step 2 tells Excel to find the number in position X in A1 and put it in the appropriate column.

Hope that helps. Ben.

 

[Rocky2889]

Ben,

My formula is used to generate UPC code. I follow this sample code accepts to calculates the check digit, and returns the finished string. The general method involves adding up the digits in the odd-numbered positions and multiplying that sum by 3; then adding to that result the values of the digits in the even-numbered positions. Divide this result by 10 and subtract the remainder from 10. If the result is equal to 10, set it to zero; otherwise this is the check digit.

Because I don't know how to put forumla in odd number positions and even number positions. May be you reading the about requirement you can put into a formula.

Thanks

Rocky

 

[sweater_vests_rock]

Rocky.

1) Enter in cell A1: 26826812345
2) Enter in cells B1->L1: =MID(A1, X, 1), where X = 1 for ColB, 2 for ColC, etc.
3) Enter in Cell M1: Your Mod Formula =MOD(10-(MOD(((B1+D1+F1+H1+J1+L1)*3)+(C1+E1+G1+I1+K1),10)),10)

The spreadsheet set-up described above

1) takes a UPC code from cell A1
2) breaks it up into 11 cells (B1->L1)
3) and then checks the validity of the UPC code via your formula in cell M1. if cell M1 = 0, then you have a valid UPC code.

The assumption I made here was that you actually had data to enter into cell A1. Are you saying that you DON'T have a UPC? Your earlier posts say that you want to generate a UPC code -- but I don't see why you provided a UPC number (26826812345).

Again, sorry to be so confused.
Ben.

 

[Rocky2889]

Ben,

Sorry making it so confusing, I attached the excel sheet for you to check.
Basically I like to be able to create consenstive number in column A and generative UPC code on column E.

On Column C is the checksum formula.

(alt[/img]


Thanks

Rocky

 

[sweater_vests_rock]

Rocky.

Is this what you want?

Ben.

'edited by Admin to remove HTML Maker code currently out of commission