Incremented alphanumeric code for multiple entries google sheets

[JTGF]

Hello,

I've created a table for warehouse inventory and I wish to automate this as much as possible using google sheets.

Column A: Manual input of the material (with specific info)
Column B: Material Type (This is from a drop down menu created in sheet 2 - the material type is fixe only 10 types)
Column C: Material ID (This is created using PROCV function in sheet 2 - its a two letter code that is associated with the material type)
Column D: Formula to create unique ID ( I was indicated this formula (see example below "=C7&TEXT(COUNTIFS(D$1:D7,C7&"*")+1,"000")") and it works fine for a static dataset, however when you add rows with new information it changes the numbers before.

The idea was to create incremented alphanumeric codes for each material codes as in the example in column D.

Unfortunately, when a new line is added the codes before change too, when they shouldn't.

Is there another way to do this without having this obstacle, and the new added entries follow the logic explained?

Material description (manual input)	Material type (drop down)	Material ID (ProcV from sheet 2)	Material ID number (formula)	How it looks in Googlesheets
bananas IND	bananas	BA	BA001	BA002
apples URG	carrots	CA	CA001	CA001
oranges BRZ	bananas	BA	BA002	BA002
Carrots US	bananas	BA	BA003
Apples US	apples	AP	AP001
CARROTS FR	carrots	CA	=C7&TEXT(COUNTIFS(D$1:D7,C7&"*")+1,"000")

Thanks you in advance for your help!!!

 

[kweaver]

Try this fill down from D2: =C2&TEXT(COUNTIFS(D$1:D1,C2&"*")+1,"000")