Formula to automatically increase 4 digit suffix if 4 digit prefix is same as another

Hi everybody,

My first post here. Hoping it will yield some success.

- In Row B I have a list of stock items by their full name. For simplicity, let's use groceries as an example. In Cell B1 "Apples - Red"; Cell B2 "Oranges"; Cell B3 "Apples - Green".
- In Row A I want to affix a stock code to each item which consists of 8 digits; the first four digits are to be letters (specifically the first four letters of the item to which the code relates) and the suffix will be 4 numbers.
- In generating the first half of the stock code I am using the formula "=LEFT(A2,4)" but in some instances the first four letters will be the same because the descriptions are similar, so I want the suffix to be the variable in this instance.
- Taking the example above, Cell A1 will show "APPL", A2 "ORAN" and A3 "APPL". As cell A1 will be the first cell to use the prefix "APPL" I want Excel to automatically detect this and assign the suffix "0001" so that Cell A1 shows "APPL0001". Similarly Cell A2 will show "ORAN0001". But as Cell A3 will also use the prefix "APPL" I want an automatic increment of 1 to indicate that Cell A3 is the second cell to use this prefix. So Excel will generate the stock code "APPL0002"

Thanks in advance for any help.

In A1:

Formula:

Please Login or Register  to view this content.

BSB

Great minds think alike, Zbor!

Just to be slightly different.

Formula:

Please Login or Register  to view this content.

Thanks to everyone who contributed. That's worked perfectly. I've tried to add to all of your reputations but it doesn't seem to have done anything when I click on the *. Don't know if that's anything to do with me being new?

Originally Posted by zbor

This wont work after 10000

Given that the requirement states a 4 digit suffix, neither will yours.

Originally Posted by zbor

Actually it will. Formating cell 0000 will give 0's for less than 10000 but keep full number after that.

My point exactly, 10000 would be a 5 digit suffix, the stipulation was 4 digits.