We've got this semi-solved using formula, but ran into issues as I need this to work in Chronological order, not placement order.

I have products and colors. Each product is a fruit in this example. There are thousand of different fruits. Each fruit has a color. There are thousands of different colors. Each color is unique to each fruit.

Each product gets a random 5 digit code assigned to it. Each color gets a 2 digit code assigned to in in numerical order. This makes up a code of product and color, example 52362-01

Example.
Apple (product) Red (color) makes the code 52362-01
if I have a Apple Yellow, the code would be 52362-02
Apple Green would be 52362-03 and so on (04, 05, 06 as the new colors are entered).

If I have a Banana Yellow, it would be a random 5 digit number again for the product 92623-01 as yellow is the first color to be added.
If there is Banana Green, it would be 92623-02 as it was the second one to be generated.

I want to be able to enter the product name (apple) in column A and the product color (red) in column B, and generate the 5 digit random product code (not numerical) with the 2 digit color code (red) in numerical order. (01, 02, 03, 04...etc.) combined in column C.

I want the code to be generated only once. If someone has deletes Apple Yellow, (52362-02) I want the code 52362-03 to remain for Apple Green. If someone adds a new color apple (Purple) I want to assign a new color code, not re-use the delete Yellow (02). So Apple Purple would be 52362-04. If someone adds another Apple Yellow later, it would get the same code it had before, 52362-02.

I know this sounds like a hard one, and in this thread http://www.excelforum.com/excel-gene...88#post3264888 spencer101 had it figured near 100% using the formula method. The only snag came when we entered product in chronological order, it would change the codes.

Very excited to see if this can be done via VBA!

Looking forward to your reply,