Auto Numbering based on another reference number

**EXCELBENCH** · 08-08-2019, 04:49 AM

Hi Gurus,

I'm enhancing my tracker and would like to seek your help on getting unique reference ids.
I have 3 conditions on how a unique reference id will be entered on Column A. Auto numbering will be based on the Project Number.
If the the project numbers are the same, the only sequence that will change will be the last 2 digits.
If the project number are totally different from the previous rows, the 4 digits & 2 digits will change.
If the project number are blank, the 4 digits & 2 digits will change.

Please see sample below. This is how I picture it. I just want it automated.
Also, it will be nice if I only get a solution that requires simple formula.

Thanks

**AliGW** · 08-08-2019, 04:56 AM

Here you go:

="S-ITS19-"&TEXT(SUMPRODUCT(1/COUNTIF($B$2:$B2,$B$2:$B2&"")),"0000")&"-"&TEXT(COUNTIF(B$2:B2,B2),"00")

**EXCELBENCH** · 08-08-2019, 04:57 AM

That was fast thank you Ma'am!

**AliGW** · 08-08-2019, 05:00 AM

No worries. It was easy!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

**EXCELBENCH** · 08-08-2019, 05:06 AM

Got it on the Admin note.

I tried it on the actual file and I see some problem.
Like if the Project number repeats on the latter part of the spreadsheet.
It doesn't give the right reference id instead it will make a new one.

**AliGW** · 08-08-2019, 05:09 AM

Attach the workbook. I think you have not copied the formula correctly.

**EXCELBENCH** · 08-08-2019, 05:13 AM

Here you go

**AliGW** · 08-08-2019, 05:17 AM

Try this:

="S-ITS19-"&IF(ISNA(MATCH(B2,B$1:B1,0)),TEXT(SUMPRODUCT(1/COUNTIF($B$2:$B2,$B$2:$B2&"")),"0000"),MID(INDEX(A$1:A1,MATCH(B2,B$1:B1,0)),9,4))&"-"&TEXT(COUNTIF(B$2:B2,B2),"00")

**EXCELBENCH** · 08-08-2019, 08:48 PM

Works perfectly. Thank you so much!

**AliGW** · 08-09-2019, 12:28 AM

Why have you removed the images and sample workbook from this thread? Please put them back. This thread should remain completely intact so that it can be of help to members in the future. Please do not vandalise your threads in this way, as it renders them useless.