Attempting to create formula to generate sequential customer ID numbers

[Diaxus]

I need to generate sequential numbers to serve as individual customer ID numbers, and these numbers must first reference a three digit number derived from the customer's zip code.

Example; there are two 3 digit codes, 503 and 504. These are derived from zip codes. I need the formula to determine which of these two I input into one cell, and from that generate a 5 digit number, which base is 00001. If done right, assuming I input 503, the first time I do so the final cell will show 503-00001. When I use 503 again in a different cell, the code will read 503-00002, and so on until it reaches 503-99999.

I need the formula to generate this range from 00001 to 99999 based solely on the 3 digit number I choose, so that the first time I use 503, I will get 503-00001, and the first time I use 504, I will get 504-00001.

I hope this makes sense.

Currently, I have created a drop down list in B column to choose the 3 digit code, and have special formatted column C to show -00000. Thus, the code can hopefully reference column B and input the formulaic result into column C.

 

[Diaxus]

I think I might have a solution, if there is a formula that looks for the last instance in a column range of a specific value, say 503. So far, all options I've found report the first instance, such as INDEX/MATCH.

 

[Trevor_S]

Assuming row 1 is headers, your formula for C2 is
=IF(B2="","",COUNTIF(B$2:B2,B2))

 

[Diaxus]

I'm going to laugh if it's that easy, not at you by any means. I spent 4 hours on this and created one of the most elaborate circular arguments I've ever made... LOL

I was going to attach my sample workbook so you could have some laughs too, but I'm not seeing an attachment option... perhaps I'm just that tired.

Anyway, I'll plug your solution in tomorrow and get back to you. Thanks for your help.

 

[Trevor_S]

Hope it works! The idea is that by fixing one end of the COUNTIF range, it will count the number of times that prefix appears up to and including the current row. It won't stop when it gets to 99,999, but I'm guessing you picked that number of digits because its higher than you think that you'll need.

You're right that you can't attach files directly in this site, however if you do have questions in future where it would be useful to show the file, you can upload it to somewhere like Google Drive or Dropbox, and paste the URL into your question. The forum recognises it as a URL and generates a link. Only thing to bear in mind is that some people will be wary about downloading from links.

 

[Diaxus]

I am so sorry I haven't gotten back to you! I got pulled of that part of the project to get other parts in order, but I will return to this when all other parts are done. I will get back to you if/when I am able to attempt the fix. Thanks again for your time and effort!