Create multiple key from Quantity cell - barcode


Hi, I have an excel sheet for product listing. Please see the attached excel book. 

I am trying to find a way to generate Product key based on Quanity. Currently, it is counting number according to the brand. Therefore, if Samsung has S10 has 5 quantities then there shoudl 5 consecutive product ID or may have Q at the end. I am here bit confused. Main objective is to create barcode for each product. Please suggest a solution. Thank you.



Selected Answer


I don't have the IDAutomationHC39M barcode font (used in column G) but your existing Product IDs in column A could be created by using this formula (in cell A2 then copied down):

=LEFT(B2,3)&"#"& COUNTIF(B$2:B2,B2)
This takes the 3 left characters of Brand (column B) then adds # plus the count of how many times that Brand has appeared since the first cell (here the dollar sign in bold is important to lock the top of the range). This gives iPh#1, Sam#1 etc. as currently.

If you want fuller Prodcut IDs including the model (so iPh_11Pro#1, iPh_11Pro#2, Sam_S9#1, Sam_S10#1 etc.), then the count can be on the Model column (which is unique I think) and more parts of your data added into the ID using this instead (for row 2 but shown in A8 of the attached sheet):

=LEFT(B2,3)&"_"&C2&"#"& COUNTIF(C$2:C2,C2)
 This assumes that you have one row (and perhaps a varying price per device if they are secondhand / previously-used).

Your Excel 2016 doesn't allow "spill" from an array formula (as Excel 356 does) but In the green cells (i1:r5) I've used an IF statement with the COLUMN function on the cell so that a number of codes (currently up to 10) appear to the right of the Quantity in column H. This is the formula in i2 (and is copied accross then down):

=IF($H2+9-COLUMN(I2)>0,LEFT($B2,3)&"_"&$C2&"#"& COLUMN(I2)-8,"")
So if for Samsung S10 there's a 5 in H5, you get 5 IDs: Sam_S10#1, Sam_S10#2, Sam_S10#3, Sam_S10#4 and Sam_S10#5 (and likewise for other quantities). Change the quantity and codes will appears and disappear to suit.

Note that you'll need to add your IDAutomationHC39M barcode font to these cells if this is what you want for your local barcodes. 

Hope this helps.



Thank you. So perfect. It does the job as I have mentioened . But I got so many brands and models and many pieces and even got parts. I need to find a way to generate id in a quicker way for each model. Likewise, may be an userform where I put the product details and quatity and it can generate the product ids in a new column horizontally not vertically otherwise it will be very awful. I am bit stuck.
Waqar (rep: 8) Apr 22, '21 at 8:24 am
Hi Waqar

Glad my answer matched your requirement- thanks for selecting it..

Not sure I can help further at this stage- I don't know your product range or the parameters which uniquely differentiate them. In fact, I'm not exactly clear why you want a new Product ID per item (of a given device)- this could be difficult to sustain over year- is this meant to control the stock too?

I think you need to think about the database that will store the product detail. Your UserForm might initiate a search of that

For a quicker way, could you use the suggestion from Variatus about taking the manufacturer's barcode (which should be unique) but appending a quantity identifier?
John_Ru (rep: 1072) Apr 22, '21 at 9:15 am
Thank you. I agree with you and Variatus. I do understand the point very well. So, for Iphone 11 I have 3 phones. Now cannot it be like IP11Q1, IP11Q2, IP11Q3 which you have already mentioned in your example workbook. Or I may just put as Quantity as it is. So each time it is scanned the Quantity cell minus 1.  But what I want is to print the barcodes. 
Waqar (rep: 8) Apr 22, '21 at 11:44 am
Sorry, not sure why you cannot print the barcodes (once you have applied your barcode font).

Remember however that the barcode alone (say on a self-adhesive label) will mean little or nothing to the human eye (so take care!).
John_Ru (rep: 1072) Apr 22, '21 at 11:59 am
Add to Discussion

International barcodes come in two main types, EAN-13 and UPC -A. Chances are thjat you have UPC codes on the products you mention. Read up on UPC codes here.

There are some digits on the far right of these numbers that you might be able to use for your purpose. They are meant to be used for discount schedules. I didn't study the subject but I presume there might be 100 numbers. If you need more (because you intend to sell more than 100 units of a product) you may have to create an entirely new system. You can buy numbers online at very low cost (google for "Barcode numbers"). Or you might create a system just for your own private use.

So, whether you take a number you get from the manufacturer or buy a number of your own or create a local system, each barcode number consists of a code on the left which identifies the product and a counter on the right that identifies each individual item.For example, let this be a provate number:-

987654 000001

Here 987654 is the product ID, for example "iPhone#1". iPhone#1 might b e 987655. There are 6 digits of numbers for each product. With these 6 digits you can number up to one million iPhones of each time and each pone will have a different number.

As you see, your setup doesn't support such a system. In your list the item number (left side above) is missing. You might just generate 5 consecutive numbers creating the entire barcode elsewhere. But you can't know which is the first number to use.

And, of course, the purpose of the list is to be able to identify each item by its number. With the list you have you may be able to give 5 different numbers (if you make sure that the same numbers weren't issued before) but you can't know which phone got which number, thereby defeating the object before you start.



Your idea is very useful. I do require a new strategy otherwise it seems complicated. 
Waqar (rep: 8) Apr 22, '21 at 8:15 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login