Thread: Comparing non consecutive cells to increment a character string

Hi,

I have a spreadsheet containing parts to name. Numbering works as follows : two letters followed by 4 numbers. The first 3 characters (two letters and first number) of the part name are chosen logically depending on what type of part it is (two letters) and what circuit # it's located on (first number).

So my sheet looks as follows :


A B C
Part type Circuit # Name
DV 0
DT 1
DV 1
DV 0

The name of the first part should be DV0001, the second DT1001, the third DV1001, the fourth DV0002.

I already know how to concatenate cells ( =A2&B2) but I want to assign the following 3 digits as the next available number (incremented) for all parts with the same type and circuit#. From a programming logic, the current NAME cell would need to look for the maximum of the last three digits of all NAME cells (column C) that have a matching string for PART TYPE and CIRCUIT#, and add +1, conserving 0s ( 008 shouldn't be 8).

So I guess this logical formula would add on to the concatenation
=A2&B2& ( max ( ??? ) +1)

Anyone has something useful ?

Thanks in advance