Generate new commodity code

I need to generate NEW COMMODITY CODES (Column I).

Cell I29 correctly displays the commodity code generation scheme which should use the 4-digit Sub-Category Code as the number prefix and add 4 digits in sequential order (i.e. 0001, 0002) for each new commodity code.

When I attempted to create a formula that looks at the row above, it did not properly generate the new commodity code. As you can see the rows are not in numerical order and are filtered (not sure if that's the reason why the formula doesn't work when looking at the row above).

If done correctly, Row I71 would stay as is, and Row I79 would end in 11020002, and I84 would end in 11020003.

Your assistance in a formula that will generate the new commodity codes for COLUMN I is greatly appreciated.

Thanks you.

Without a sample worksheet, but try to guess:

In I46:

=F46&TEXT(COUNTIF($F$1:F46,F46),"0000")

or:

=IF(F46="","",F46&TEXT(COUNTIF($F$1:F46,F46),"0000")

Drag down.