Find next available product code dependent on category

yorwos

New Member
Joined
Jun 3, 2019
Messages
4
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
Joined
Oct 20, 2009
Messages
7,812
Office Version
365
Platform
Windows
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
Joined
Jun 3, 2019
Messages
4
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
<code>ER044747</code>
05
<code>ER044748</code>
05
<code>ER044749</code>
05
<code>ER044750</code>
05
<code>ER050009</code>
05
<code>ER069317</code>
18
<code>ER069318</code>
18
<code>ER031132</code>
17
<code>ER031134</code>
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
 

yorwos

New Member
Joined
Jun 3, 2019
Messages
4
Codes
Group
Column1
ER0447475ER50010
ER0447485ER50010
ER0447495ER50010
ER0447505ER50010
ER0500095ER50010
ER06931718ER1
ER06931818ER1
ER03113217ER1
ER03113417ER1
22ER1
22ER1
31ER1
1ER1
1ER1
5ER50010
17ER1
17ER1

<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
 

Forum statistics

Threads
1,084,753
Messages
5,379,663
Members
401,620
Latest member
Ankur Teotia

Some videos you may like

This Week's Hot Topics

Top