Trying to auto-generate SKUs in Google Sheets using prefix and sequence number but it breaks when sh-CodePudding

I want to automatically generate unique, incremented ids (SKUs) for products in my inventory using Google Sheets. These SKUs should be immutable and not change when the sheet is sorted. This screenshot shows my flawed attempt to do this:

The formula I have for column A is:

=if($B2<>"",$B2 & TEXT(COUNTIF($B$2:$B2,$B2),"-0000"),"")

That formula appears to give the desired result. However, if I sort by the values in Column C, SKUs become associated with different Item Names, which is a problem. How can I modify the spreadsheet to get immutable, auto-generated SKUs?

Update:

I would like to be able to add additional rows/SKUs to the sheet over time
Here is a link to the sample spreadsheet

CodePudding user response：

I've added a sheet ("InventoryMaster(Unsorted") to your spreadsheet.

This sheet would be used to add your prefixes and items only. Once added, you would never sort, insert rows, delete rows or items, etc. Just keep adding the next prefix and item into the next open row, in Col B and Col C. Col A has a formula that will create the static SKUs from that unchanging and ordered information. (I've hidden that formula-containing Col A, so that you only have to enter the manual data and don't have to worry about accidentally overwriting the formula or its results. It's still there at all times, serving its purpose in the background.)

Then, other sheets (see my added "Erik Help" sheet) can reference that "immutable" list, giving you the ability to sort without losing SKU connection.

Here is the A1 formula from the 'InventoryMaster(Unsorted)' sheet:

=ArrayFormula({"SKU";IF(B2:B="",,B2:B&TEXT(COUNTIFS(B2:B,B2:B,ROW(B2:B),"<="&ROW(B2:B)),"-0000"))})

And here is the SKU-assigning formula from A1 of the "Erik Help" sheet:

=ArrayFormula({"SKU";IF(B2:B="",,VLOOKUP(B2:B&C2:C,{'InventoryMaster(Unsorted)'!B2:B&'InventoryMaster(Unsorted)'!C2:C,'InventoryMaster(Unsorted)'!A2:A},2,FALSE))})