Trying to create a unique identifier based on column and running into duplicates

[cmac7755]

I have a data set split into categories like 7511, 7512, etc. These categories are listed in ascending order in column A. Each category has subsets of data which consist of numerous columns shown as the letters below. I am trying to create a unique identifier in column F for each subset of data based on the category number to be used for other work. I am running into the issue of duplicate UI values if a category has more than 10 subsets of data. The current formula I am using is:

=IF(A2=A1,F1+1,A1&1).

Is there anyway to amend this formula so the 10th row per category and higher show up as ####10, ####11, etc. instead of ###20, ###21 and so on.

Category data UI
7511 abcdefg 75111
7511 abcdefg 75112
7511 abcdefg 75113
7511 abcdefg 75114
7511 abcdefg 75115
7511 abcdefg 75116
7511 abcdefg 75117
7511 abcdefg 75118
7511 abcdefg 75119
7511 abcdefg 75120
7511 abcdefg 75121
7512 abcdefg 75121
7512 abcdefg 75122
7512 abcdefg 75123

[FlameRetired]

We need more information .... in context.

Please see upload instructions at the top of this page. HOW TO ATTACH YOUR SAMPLE WORKBOOK:

[TMS]

What version of Excel are you using? Is it 2010? Please update your profile.

[cmac7755]

Microsoft Excel 2016

[wk9128]

What do you want to do if there is a duplicate of F12 and F13?


The following formulas will encounter duplicates
Cell F2 formula , Drag down

Other option

[Gregb11]

Is there a limit to the number size in column F? How many rows can have the same category number?

Anyway, you can try this in cell F2 and copy down. You can change the 10 to 100 or 1000 depending on the answers to my above questions.

=IF(A2=A1,F1+1,A2*10&1)

[menem]

It's depending on how many per category.
The following formula can deal to 999 items per category.

F2
=A2&TEXT(COUNTIF(A$1:A2,A2),"000")

Regards.