+ Reply to Thread
Results 1 to 16 of 16

UPC Barcode Check Digit as a conditional format

  1. #1
    Registered User
    Join Date
    05-13-2021
    Location
    new york
    MS-Off Ver
    Excel for mac 16.49
    Posts
    11

    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
    Last edited by help_at_work; 05-13-2021 at 02:52 PM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,823

    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. #3
    Registered User
    Join Date
    05-13-2021
    Location
    new york
    MS-Off Ver
    Excel for mac 16.49
    Posts
    11

    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
    Last edited by help_at_work; 05-13-2021 at 04:49 PM.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,823

    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. #5
    Registered User
    Join Date
    05-13-2021
    Location
    new york
    MS-Off Ver
    Excel for mac 16.49
    Posts
    11

    Re: UPC Barcode Check Digit as a conditional format

    Attached i hope
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,823

    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. #7
    Registered User
    Join Date
    05-13-2021
    Location
    new york
    MS-Off Ver
    Excel for mac 16.49
    Posts
    11

    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. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,823

    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. #9
    Registered User
    Join Date
    05-13-2021
    Location
    new york
    MS-Off Ver
    Excel for mac 16.49
    Posts
    11

    Re: UPC Barcode Check Digit as a conditional format

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

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,823

    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. #11
    Registered User
    Join Date
    05-13-2021
    Location
    new york
    MS-Off Ver
    Excel for mac 16.49
    Posts
    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. #12
    Registered User
    Join Date
    05-13-2021
    Location
    new york
    MS-Off Ver
    Excel for mac 16.49
    Posts
    11

    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. #13
    Registered User
    Join Date
    05-13-2021
    Location
    new york
    MS-Off Ver
    Excel for mac 16.49
    Posts
    11

    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. #14
    Registered User
    Join Date
    05-13-2021
    Location
    new york
    MS-Off Ver
    Excel for mac 16.49
    Posts
    11

    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. #15
    Registered User
    Join Date
    05-13-2021
    Location
    new york
    MS-Off Ver
    Excel for mac 16.49
    Posts
    11

    Re: UPC Barcode Check Digit as a conditional format

    here is the sample file 2
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    05-13-2021
    Location
    new york
    MS-Off Ver
    Excel for mac 16.49
    Posts
    11

    Re: UPC Barcode Check Digit as a conditional format


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula for calculating a barcode check digit
    By tukae in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-02-2020, 12:51 PM
  2. Barcode Check Digit Calaculation
    By ALC1167 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-24-2017, 01:59 PM
  3. [SOLVED] Calculate check digit forEAN-8 barcode
    By yanceygm in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-19-2016, 12:16 AM
  4. Barcode Check Digit Formula
    By strud in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-16-2013, 10:12 AM
  5. Mod 11 - Check Digit Barcode
    By pr4t3ek in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-08-2011, 04:30 AM
  6. Macro to force format and check barcode check digit
    By Code Flunkie in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-02-2009, 10:27 AM
  7. GTIN Barcode Check Digit Help
    By brohlfs in forum Excel General
    Replies: 7
    Last Post: 05-15-2009, 11:17 AM

Tags for this Thread

Bookmarks

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