# UPC Barcode Check Digit as a conditional format

1. ## UPC Barcode Check Digit as a conditional format

Hello all i have been reading this is the first i am writing .

i have read on how to get a upc check digit from a calculation most have more then one cell to work from.

what i have is a product list for about 3000 items each with a barcode number but if the item is not currently in production
it will only have 11 digits so when in production we manually add the 12 check digit to the sheet.

i have conditional formatting on the column (in this case its D )

=SUM((LEN(\$D1))=11) which changes the background of the cell to grey
=SUM((LEN(\$D1))=11) which changes the background of the cell to green

so my question ?
i would like to add another rule that will add the check digit to the number that meets the following condition if column A is not unique
Column A is our model number which if not in use is N/A and if in use it is a unique number
can this be done ? or am i chasing my tail

barcode number is 87964000000 for a sample and the check digit is 4

as for the calculations for the check digit i got the formula from the message boards here :-) thank you for that

Step Two: Sum all digits in odd position and multiply the result by 3. (8+9+4+0+0+0) * 3 = 63
Step Three: Sum all digits in even position. (7+6+0+0+0) = 13
Step Four: Sum the results of step three and four: 63+13 = 76
Step Five: Divide the result of step four by 10. 76 / 10 = 7.6 the 10 - 6 = 4
The check digit is the number which adds the remainder to 10.
In our case, divide 76 by 10 we get the remainder 6
The check digit then is the result of 10 - 6 = 4

hope this makes some sense to someone for help thanks in advance
peace be with you

Screen Shot 2021-05-13 at 2.51.38 PM.png

2. ## Re: UPC Barcode Check Digit as a conditional format

With Barcode in A8

=A8+(10 -MOD((SUMPRODUCT(MID(A8,ROW(A\$1:A\$11),1)*(MOD(ROW(A\$1:A\$11),2)))*3+SUMPRODUCT(MID(A8,ROW(A\$1:A\$11),1)*(MOD(ROW(A\$1:A\$11)-1,2)))),10))

Enter with Ctrl+Shift+Enter

3. ## Re: UPC Barcode Check Digit as a conditional format

thank you for getting back to me.
the formula supplied does work for the number supplied but when plugged into the file it falls apart when used with additional numbers as well as requiring an additional entry point for the barcode to be put in.
is it possible to have the formula to effect the entered cell in the formatting rule
i attached to this the sample excel sheet that has the conditioning to the Columns

Screen Shot 2021-05-13 at 4.48.04 PM.png

Screen Shot 2021-05-13 at 4.48.24 PM.png

4. ## Re: UPC Barcode Check Digit as a conditional format

Answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

5. ## Re: UPC Barcode Check Digit as a conditional format

Attached i hope

6. ## Re: UPC Barcode Check Digit as a conditional format

the formula was only meant to provide check digit not as a CF test: it is an array formula so cannot even be used in CF

=D3&(10 -MOD((SUMPRODUCT(MID(D3,ROW(A\$1:A\$11),1)*(MOD(ROW(A\$1:A\$11),2)))*3+SUMPRODUCT(MID(D3,ROW(A\$1:A\$11),1)*(MOD(ROW(A\$1:A\$11)-1,2)))),10))

Note: 87964001056 results in MOD(100,10) =100 so we get 8796400105610 which obviously wrong.

7. ## Re: UPC Barcode Check Digit as a conditional format

so it is not possible to have a conditional format in the column D to return in the same cell the full barcode number with check digit if the condition in column A as a unique value is met .

8. ## Re: UPC Barcode Check Digit as a conditional format

No: Conditional Formatting cannot change a cell value. Nor can you have both a formula AND value in the same cell so you will need to have a separate column for the full barcode number using formulae. If you want it in the same cell, you will need VBA.

9. ## Re: UPC Barcode Check Digit as a conditional format

yea as a novice beginner i would not have a clue about that.

10. ## Re: UPC Barcode Check Digit as a conditional format

Correction to formula:

=D3&MOD(10 -MOD((SUMPRODUCT(MID(D3,ROW(A\$1:A\$11),1)*(MOD(ROW(A\$1:A\$11),2)))*3+SUMPRODUCT(MID(D3,ROW(A\$1:A\$11),1)*(MOD(ROW(A\$1:A\$11)-1,2)))),10),10)

To avoid VBA coding why not simply put the Barcode with check digit in another column?

11. ## Re: UPC Barcode Check Digit as a conditional format

thank you again just trying to implement this with 2000 existing PRODUCT UPC GTIN-12 and CASE PACK UPC GTIN-14
if i place a another column to display just the check digit for all existing numbers what would that look like ?

12. ## Re: UPC Barcode Check Digit as a conditional format

=MOD(10 -MOD((SUMPRODUCT(MID(D5,ROW(A\$1:A\$11),1)*(MOD(ROW(A\$1:A\$11),2)))*3+SUMPRODUCT(MID(D5,ROW(A\$1:A\$11),1)*(MOD(ROW(A\$1:A\$11)-1,2)))),10),10)

only issue is i would need to implement this for each cell ? D5 next row D6 etc etc ?

13. ## Re: UPC Barcode Check Digit as a conditional format

=MOD(10 -MOD((SUMPRODUCT(MID(G3,ROW(A\$1:A\$13),1)*(MOD(ROW(A\$1:A\$13),2)))*3+SUMPRODUCT(MID(G3,ROW(A\$1:A\$13),1)*(MOD(ROW(A\$1:A\$13)-1,2)))),10),10)

would be used to implement the 2of5 code since it is the same math but to the 13th character

14. ## Re: UPC Barcode Check Digit as a conditional format

would like to know what i need to put in the code so that it would be for all column and not each individual cell

15. ## Re: UPC Barcode Check Digit as a conditional format

here is the sample file 2

16. ## Re: UPC Barcode Check Digit as a conditional format

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1