Find next available code

[Rish12]

Hi,

I have a set of codes and I want to find the next available code. Below is an example for it. So if I enter 02-002 then I should get the next code available which is 02-002-0004. I am looking for an excel formula instead of VBA code. Thanks for your assistance.

Product Code	Input	Available Code
01-041-0001	02-002	02-002-0004
02-001-0001
02-001-0002
02-002-0001
02-002-0002
02-002-0003
02-003-0001
02-003-0002
02-003-0003
02-003-0004

 

[arthurbr]

Perhaps

=B2&"-"&TEXT(SUMPRODUCT(--(LEFT($A$2:$A$11,6)=$B2))+1,"0000")

 

[Rish12]

Perhaps

=B2&"-"&TEXT(SUMPRODUCT(--(LEFT($A$2:$A$11,6)=$B2))+1,"0000")

Works perfectly, Thanks a lot.

 

[arthurbr]

Most welcome