I have an Excel worksheet with two columns: Column A and Column B.
Column A has all existing product codes.
Column B has all existing and new product categories. Product categories are two-digit numbers (with leading zeros, as required). (The product categories on existing products are actually irrelevant to this question.)
Old product codes consists of two parts: Two letters and six digits. The letters are always "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 four-digit number
Both the product category and the four-digit number have leading zeroes, as required. For example, if product category is 14, then all the new product codes in this category should between ER140001 and 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.
To get array of products in category
Then use a formula with
row(indirect("$1:$9999")) combined with
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 sequential numbers would work fine as well. No harm knowing how to do both, though.
A B ⑴ ER044747 05 ⑵ ER044748 05 ⑶ ER044749 05 ⑷ ER044750 05 ⑸ ER050009 05 ⑹ ER069317 18 ⑺ ER069318 18 ⑻ ER420001 17 ⑼ ER031134 17 ⑽ 17 ⑾ 17 ⑿ 05 ⒀ 22 ⒁ 22 ⒂ 42The 05 product (Row 12) would take any value between ER050001 and ER059999 except ER050009 since it was used before (in row 5).
The first 17 (Row 10) would take any value between ER170001 and ER179999. And the 2nd 17 (Row 11) would exclude the used value above it.
The 42 (Row 15) would take any value between ER420002 and ER429999. ER420001 is unavailable since it was used in row 8.
So the result could be something like this:
A B ⑴ ER044747 05 ⑵ ER044748 05 ⑶ ER044749 05 ⑷ ER044750 05 ⑸ ER050009 05 ⑹ ER069317 18 ⑺ ER069318 18 ⑻ ER420001 17 ⑼ ER031134 17 ⑽ ER170001 17 ⑾ ER170002 17 ⑿ ER050001 05 ⒀ ER220001 22 ⒁ ER220002 22 ⒂ ER420002 42where the bold italic values in cells A10:A15 are created by the formula.