|This is the perfect time to introduce the concept of a "helper column". Helper columns are often used to store intermediate results that can help simplify or provide input for other formulas.|
In your case, you need to know how many instances of each number you have so that you can created the incrementing sequence. To obtain that count, we will use a helper column.
For simplicity sake, I'll use Column A as the helper column.
With your "Numbers" in C1:C9, enter this formula in A2 and drag it down to A9.
Note the use of the dollar signs to lock C2 as the start of the range. You should end up with a table that looks like this:
A B C
1 Unique ID Number
2 1 1
3 1 2
4 2 2
5 3 2
6 1 3
7 1 4
8 2 4
9 1 5
You can see that it incremented the count for each number as goes down through A2:A9.
Now in B2 enter this and drag it down:
="0000" & C2 & "-" & A2
With that formula I am assuming that you will never have more than 9 instances of a number. If you will have more than 9, then you need a way to drop one of the leading zeros. One way to do that would be to use an IF function to check the length of the numbers in Column C to determine how many leading zeros to use, e.g.
This will use 3 leading zeros for original data with 2 digits and 4 leading zeros for orginal data with 1 digit. You could add more IF clauses for 3 digits, 4, etc.
Note: You can use any column you want as the helper column although I don't suggest using something like Column ZZ if the rest of your sheet is only using e.g. A:D. Using a column way out to the right will make your spreadsheet unnecessarily wide. You can hide the helper column if you don't want to see it.
Let me know if this works for you.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.