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.

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

To get array of products in category

Then use a formula with `row(indirect("$1:$9999"))`

combined with `min(IF(IFNA(match(`

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 ⑽where the bold italic values in cells A10:A15 are created by the formula.17 ⑾ER17000117 ⑿ER17000205 ⒀ER05000122 ⒁ER22000122 ⒂ER22000242ER420002

`VLOOKUP`

,`MATCH`

or`COUNTIF`

to see which ones already exist. Filter on the helper column and copy all new product codes. – cybernetic.nomad Jun 3 '19 at 18:44as textand not a screenshot.However, (1) It seems like unnecessarily much data. You have posted 18 rows. I believe that you could illustrate everything that you need to illustrate with 8 or 9 rows, and it would be easier to read. (2) Your sample data seem to be incomplete. I understand from your explanation that you have 1100 rows like the ones you show, but also 500 rows where Column A is blank. You should show a few of those, too. … (Cont’d) – Scott Jun 3 '19 at 18:47describewhat you want more clearly. Do you want sequential numbers or random numbers? – Scott Jun 3 '19 at 18:47