Selected Answer

Waqar

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.