Find next available product code dependent on category

[yorwos]

I have two columns Column A and Column B Column A has all existing product codes Column B has all existing and new product categories


Old Product codes consists of two parts two letters and a 6-digit number the letters always remain the same ER the numbers were picked at random. So existing product codes are between ER000001 and ER999999


New product codes will follow a new pattern "ER" + Product Category + a number For example, if product category is 14 All the new product codes in this category should be ER140001 up to ER149999


I need a formula that will create new product codes but not use any of the old ones


I have tries a million things and I am afraid I cant find a solution.

<code>
</code>

 

[steve the fish]

Hi. This will work:

="ER"&MAX(IF(LEFT($A$1:$A$1000,4)="ER"&TEXT(B1,"00"),0+MID($A$1:$A$1000,3,6)))+1

Enter CTRL-SHIFT-ENTER or it wont work.

 

[yorwos]

I am rewriting the post just to be more clear and precice.

I have two columns,Column A and Column B.
Column A has all existing product codes.
Column B has all existing and new product categories.

Old Product codes consists of two parts Two letters and a 6-digit number .
The letters always remain the same "ER" while the numbers were picked at random. So existing product codes are between ER000001 and ER999999

New product codes will follow a new pattern

"ER" + Product Category + a number
​

For example, if product category is 14, then All the new product codes in this category should be ER140001 up to ER149999

I need a formula that will create new product codes based on the product category in column B but not use any of the old codes above in column A.
Right now column A has 1100 products with their respective categories in column B and now we added another 500 rows with column B filled with categories but column A for those rows is still empty.

I am thinking about a formula using reference style.

<code>If(RC2=R2C2:R[-1]C2,MID(R2C:R[-1]C,3,6))

</code> To get array of products in category
Then use a formula with <code>row(indirect("$1:$9999"))</code> combined with <code>min(IF(IFNA(match(</code>
To check if a value is used... I am kinda lost.

The best case would be to have random numbers from The available left, but sequencials would work fine as well. No harm knowing how to do both though
[TABLE="width: 500"]
<tbody>[TR]
[TD]<code>ER044747</code>
[/TD]
[TD]05
[/TD]
[/TR]
[TR]
[TD]<code>ER044748</code>
[/TD]
[TD]05
[/TD]
[/TR]
[TR]
[TD]<code>ER044749</code>
[/TD]
[TD]05
[/TD]
[/TR]
[TR]
[TD]<code>ER044750</code>
[/TD]
[TD]05
[/TD]
[/TR]
[TR]
[TD]<code>ER050009</code>
[/TD]
[TD]05
[/TD]
[/TR]
[TR]
[TD]<code>ER069317</code>
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD]<code>ER069318</code>
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD]<code>ER031132</code>
[/TD]
[TD]17
[/TD]
[/TR]
[TR]
[TD]<code>ER031134</code>
[/TD]
[TD]17
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]31
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]01
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]01
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]05
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]17
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]17
[/TD]
[/TR]
</tbody>[/TABLE]

<code>


</code> I.e. the 05 one, would take any value between ER050000 and ER059999 except ER050009 since it was used before
17 one would take any value between ER170000 and ER179999 And the 2nd 17 one would exclude the used value above it

 

[steve the fish]

So you completely ignored what I wrote?

 

[yorwos]

[TABLE="width: 202"]
<tbody>[TR]
[TD]Codes
[/TD]
[TD="align: center"]Group
[/TD]
[TD]Column1[/TD]
[/TR]
[TR]
[TD]ER044747[/TD]
[TD="align: center"]5[/TD]
[TD]ER50010
[/TD]
[/TR]
[TR]
[TD]ER044748[/TD]
[TD="align: center"]5[/TD]
[TD]ER50010[/TD]
[/TR]
[TR]
[TD]ER044749[/TD]
[TD="align: center"]5[/TD]
[TD]ER50010[/TD]
[/TR]
[TR]
[TD]ER044750[/TD]
[TD="align: center"]5[/TD]
[TD]ER50010[/TD]
[/TR]
[TR]
[TD]ER050009[/TD]
[TD="align: center"]5[/TD]
[TD]ER50010[/TD]
[/TR]
[TR]
[TD]ER069317[/TD]
[TD="align: center"]18[/TD]
[TD]ER1[/TD]
[/TR]
[TR]
[TD]ER069318[/TD]
[TD="align: center"]18[/TD]
[TD]ER1[/TD]
[/TR]
[TR]
[TD]ER031132[/TD]
[TD="align: center"]17[/TD]
[TD]ER1
[/TD]
[/TR]
[TR]
[TD]ER031134[/TD]
[TD="align: center"]17[/TD]
[TD]ER1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]22[/TD]
[TD]ER1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]22[/TD]
[TD]ER1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]31[/TD]
[TD]ER1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]1[/TD]
[TD]ER1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]1[/TD]
[TD]ER1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]5[/TD]
[TD]ER50010[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]17[/TD]
[TD]ER1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]17[/TD]
[TD]ER1[/TD]
[/TR]
</tbody>[/TABLE]

No, but these are the results from your formula, so i thought i wasn't clear
="ER"&MAX(IF(LEFT($A$2:$A$1000,4)="ER"&TEXT(B2,"00"),0+MID($A$2:$A$1000,3,6)))+1

 

[yorwos]

So you completely ignored what I wrote?

I might have done something wrong, and thank you ofcourse for your time.