Icrement Increase until Cell change, reset

[lipipe]

Hello!

We are deploying a new computer system in our company and I have to create new account numbers for our customers. The field is 7 ALPHA-NUMERIC and I have decided to make it the first three letters of the name and the last 4 digits. I have sorted the customers and I am trying to create a formula that will increase the 4 digits by my set increment and then reset when it hits a new set of three characters. For example:

I have 7 companies:

ABC WAREHOUSE COMPANY
ABC SUPPLY COMPANY
BEST AUTO SUPPLY
CHUCKS SUPER STORE
DAVIS SUPPLY
KELLY AUTO BODY
KELLY SUPER STORE

I use the first three characters of the customer and the last 4 I need to start at 10 and increment by 10 then reset to 10 and start adding 10 until the next change so I get the following results:

ABC0010
ABC0020
BES0010
CHU0010
DAV0010
KEL0010
KEL0020

I appreciate any help on how to achieve this. Thanks in advance!

Jack

 

[Marcelo Branco]

Hi Jack,

Welcome to the forum

Try this in B1 (assuming your data in A1:A7)

=LEFT(A1,3)&TEXT(COUNTIF($A$1:A1,LEFT(A1,3)&"*")*10,"0000")

copy down

HTH

M.