Find next available product code dependent on category

yorwos

New Member
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

Well-known Member
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

New Member
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
 ER044747 05 ER044748 05 ER044749 05 ER044750 05 ER050009 05 ER069317 18 ER069318 18 ER031132 17 ER031134 17 22 22 31 01 01 05 17 17

<tbody>
</tbody>

<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

Well-known Member
So you completely ignored what I wrote?

yorwos

New Member
 Codes Group Column1 ER044747 5 ER50010 ER044748 5 ER50010 ER044749 5 ER50010 ER044750 5 ER50010 ER050009 5 ER50010 ER069317 18 ER1 ER069318 18 ER1 ER031132 17 ER1 ER031134 17 ER1 22 ER1 22 ER1 31 ER1 1 ER1 1 ER1 5 ER50010 17 ER1 17 ER1

<tbody>
</tbody>

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

New Member
So you completely ignored what I wrote?
I might have done something wrong, and thank you ofcourse for your time.