Comparing non consecutive cells to increment a character string

[Virgule]

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

 

[AlphaFrog]

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Part type</td><td style="font-weight: bold;text-align: center;;">Circuit #</td><td style="font-weight: bold;text-align: center;;">Name</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">DV</td><td style="text-align: center;;">0</td><td style="text-align: center;;">DV0001</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">DT</td><td style="text-align: center;;">1</td><td style="text-align: center;;">DT1001</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">DV</td><td style="text-align: center;;">1</td><td style="text-align: center;;">DV1001</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">DV</td><td style="text-align: center;;">0</td><td style="text-align: center;;">DV0002</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=A2&B2&TEXT(<font color="Blue">SUMPRODUCT(<font color="Red">--(<font color="Green">$A$2:A2=A2</font>),--(<font color="Green">$B$2:B2=B2</font>)</font>),"000"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=A3&B3&TEXT(<font color="Blue">SUMPRODUCT(<font color="Red">--(<font color="Green">$A$2:A3=A3</font>),--(<font color="Green">$B$2:B3=B3</font>)</font>),"000"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=A4&B4&TEXT(<font color="Blue">SUMPRODUCT(<font color="Red">--(<font color="Green">$A$2:A4=A4</font>),--(<font color="Green">$B$2:B4=B4</font>)</font>),"000"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=A5&B5&TEXT(<font color="Blue">SUMPRODUCT(<font color="Red">--(<font color="Green">$A$2:A5=A5</font>),--(<font color="Green">$B$2:B5=B5</font>)</font>),"000"</font>)</td></tr></tbody></table></td></tr></table><br />

If you have Excel 2007 or later, you could use COUNTIFS instead of SUMPRODUCT
C2
=A2&B2&TEXT(COUNTIFS($A$2:A2,A2,$B$2:B2,B2),"000")

 

[Virgule]

You sir are a genius!

do you mind explaining what the -- is for ?

Thank you so much

 

[AlphaFrog]

The -- is called a double unary and it coerces the TRUE\FALSE results within the Sumproduct formula to 1s and 0s so they can be summed (or counted in this case).

If you do a search for something like "SUMPRODUCT double unary", you will find a more detailed explanation.