1

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
⒂                42
The 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    42
where the bold italic values in cells A10:A15 are created by the formula.

  • The easiest thing may be to generate all possible values, then use a helper column with a 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:44
  • Thank you for posting sample data as text and 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:47
  • (Cont’d) …  (3) Most importantly, you should show an example of the results that you want.  (4) Also, please describe what you want more clearly.  Do you want sequential numbers or random numbers? – Scott Jun 3 '19 at 18:47
1

Go to cell A1101; i.e., the first blank cell in Column A.  (This corresponds to A10 in the sample data in the question.)  Enter

="ER" & TEXT(B10,"00") & TEXT(MIN(IF(ISERROR(MATCH("ER"&TEXT(B10,"00")&TEXT(ROW($1:$9999),"0000"),A$1:A9,0)),ROW($1:$9999),10000)),"0000")

(replacing both occurrences of the number 10 with the actual row number) and press Ctrl+Shift+Enter.  If you’re already using R1C1 style, use

="ER" & TEXT(RC[1],"00") & TEXT(MIN(IF(ISERROR(MATCH("ER"&TEXT(RC[1],"00")&TEXT(ROW(R1:R9999),"0000"),R1C:R[-1]C,0)),ROW(R1:R9999),10000)),"0000")

(You don’t need to make any adjustments because RC[1] means the next cell over in the current row, without needing to use the actual row number.)  Then drag/fill down to cover all the rows where Column B has a value.

This will generate (in each row) the next available “ER” + Product Category + nnnn value; i.e., the lowest possible value that hasn’t been used previously (i.e., in Column A, above the current row).

                spreadsheet

(The bold values in cells A10:A15 are generated by the formula; the rest are constants.)

| improve this answer | |
  • Note that there’s no need to say ROW(INDIRECT("$1:$9999")); you can use ROW($1:$9999). – Scott Jun 4 '19 at 4:18
  • Works perfectly! I can't thank you enough. The solution is so easy an elegant – yorwosA Jun 4 '19 at 6:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.