4 digit code for similar product, new product unique code

Here's a quick and dirty method for doing this. Formulas in the yellow cells. Just copy down as need be.

I'm sure there are tidier ways of doing the same...


EDIT: Apologies, I didn't read the post fully. This method does the 1001, 1002, 1003 approach.
It would probably be easier to set up a lookup table and manually allocate the code number to any new product and just have that feed in using VLOOKUP.

Sweet, thanks for the quick reply!

It works great! Any way to have the new number generated not go in numerical order but rather 1000-9999 number.

Perhaps make the random list (may creative ways of doing this) in a new tab from Col A 1000 to 9999, then have the new numbers only allow selection of using one per product?

Even if it just jumped up and down by 562's. I added +562 to the end of your formula. This works fine, but keeps jumping up in numbers. Perhaps when it hits 9999, it could start going back down the list of numbers again. I'd imagine this would create a fairly unique set of number?

See my edit above...

Why NOT just use sequential numbers?

Is there a maximum number of products?

I'm guessing as they'll have 4 digit codes then 9000 is the limit(?)

Then make a random list of numbers and assign them sequentially from that.

Working on it now.

This file may give you some ideas. As fruit is added to column B a number is generated in column A which is used in a VLOOKUP of an array of randomized numbers. When the entry is complete in column B, a 4 digit code from the list of randomized numbers is returned. In addition, a summarized listing is created that is used to fill in repeats of numbers for repeated fruit entries.

Hi,

Apologies for not getting back to this post yesterday. My laptop was having a funny half hour and I couldn't connect to the forum for some reason.

Anyway, attached is my approach to this problem. It's similar to the offering above from newdoverman but slightly simplified.

There are notes in the file that will hopefully explain what is happening.

Hope it does what you need.

S.

I cleaned up the coding and moved the "helper" table and summary table to separate sheets.

The formulae are inserted to row 10,000.

Hi newdoverman,

I'm not sure I understand the significance of Sheet 2 in your method.

Also, worth noting that the OP shows Excel 2003 as his software version. This will mean the IFERROR aspect of your version will not work.

S.

Thank you Spencer101. I didn't notice the Excel version number. I have made amendments to accommodate the version. The second sheet is an information sheet only that can be deleted without consequence if not wanted.

Depending upon the frequency of the updates, the 4 digit code may not be sufficient for very long.

No problem. Happy to help.
I'm online for a little while if you need further help with it.

Hey guys. Can I get one more modification to this worksheet? Last time, I promise

So we have Apples, Orange and Grapes. Each fruit gets a unique number assigned to it per the worksheets above. Now, I want to assign a color what translates to a 2 digit number.

Each product has a color, and the color gets a number in the order its generated. The colors are unique to the first code.
-If red is generated first for Apple (4419), then it gets 01. If green is generated next, it gets 02. Makes 4419-01 Apple Red and 4419-02 for Apple Green.
-If green is generated first for Grape (5520), then it gets 01. If red gets entered next it gets 02. Makes 5520-01 for Grape Green and 5520-02 for Grape Red.
-Orange (7723) has only one color...Orange It would be 7723-01

You guys think this is possible?

Would you guys mind making this a 5 digit number for the product code per newdoverman's suggestion. I may run out faster than planned with only 4 digits. 01000 to 99999 would last me a long time. I'd like to have the leading zero for the 4 digit numbers if possible.

I've already left stars for newdoverman and Spencer101

Looking forward to the final worksheet. You guys are epic!

Would the fruit and the colour be stored in separate columns or the same?

Leave it with me. I'll have it back to you ASAP. Hopefully

One quick question.... Are you actually using Excel 2003 like your details show?

If so, that could cause a problem with upping the product codes to 99999 as that version of Excel will only allow 65000 (ish) rows in a worksheet.

Glad to help.

Just saw your message for further requirements. Will think about it after supper.

I suggest that you assign numbers to the colours. This will reduce the calculations in a large worksheet. Having fixed colour coding will enable you to have appropriate colours. Eg. Apples, tomatoes and peppers can all be red or green. Having a fixed colour coding would allow you to identify everything that is the same colour (if that ever became an issue or requirement)

As mentioned before, can you confirm the version of Excel that you are using?

How many products will you be dealing with?

When you update your listings, do you delete the listings that have been updated?

If your products can be classified into different categories, you can take advantage of this by having codes for those categories instead of going to a 5 digit number. Category classification is superior to having larger numbers....I'm a retired trained records management person who assigned file numbers to millions of records...that is why I ask the questions that I have asked.

Colour codes have been added but has only limited testing.

Hope this is what you want.

EDIT: Deleted

Hi, that wasn't my version. It was NDM's. I deleted my post above because when I got home and tried it out, I found it didn't quite work too.

Cooking dinner now, but will continue looking at this after
I know it can be done, just can't quite get it.

I will not be beaten by it

Well, I think I may have done it!
You'll have to test it with your real data (I presume this is not really about fruits of various colours), but it seems to work.

Three helper columns (A:C) that can be hidden and the rest I shall let you work out.

The Product Code only has 64,000 entries at the moment, but it's easily extended to whatever you need. I had to restrict the file size, hence not the full 99,999 you asked for earlier in the thread.

The colour code will go up to 99 at present but again, can be extended if need be.

Let me know if it works.

S.

Ignore the version above. Having had time to think about it a little more, it really doesn't do quite what I hoped.
Back to the drawing board....

IThat version doesn't work I promise. But the good news is, I do have the solution.

As soon as I get back home from work I'll post it for you.

Watch this space...

Well... This time I really do think I've done it.
Took a lot of digging around down the back of the internet, but I ended up back at an old post on this very forum for the answer I was searching for.

The problem with the last version I posted was it looked for the last colour number associated with any given fruit and added one on to that to create the next number.
This didn't work as if you had red apple, red apple, green apple, red apple, your colour codes would be 1, 1, 2, 1. Then if the next one was yellow apple the formula would look for the last value for apple and find 1 because that's the one furthest down the column. Then add 1 to that and you ended up with both green and yellow being 2.

I'm pretty sure that makes no sense the way I've explained it, but it does to me

So I needed to find a way of doing MAXIF, which as we all know, doesn't exist in Excel at present. I know of a way using an array formula to do this but for some reason that didn't work in this project. But thanks to this forum I learned of a non-array formula way of doing MAXIF (Thanks to tigeravatar!). So it now looks up the max value against apple and adds one to that for a combination that doesn't yet exist.

I shall stop waffling and let you look at the file.

I'm sure there is a much neater way of doing this, and perhaps someone far more experienced with the dark art that is Excel will come up with one. But I'm done on this particular one for now

If this doesn't work then I'm out of ideas!!!!

Let me know

S.

It makes no real difference. Even with a formula the result will always be 1.

Spencer101...just working on this some more. Very impressed this works!

Ran into a interesting scenario...If I add more products and colors, but leave a gap between any cells, it calculates -01 for all colors.

Using your example sheet above, enter values in D24 "Grape" "Green", in D25 "Grape" "Red" will both return the same code. Row 23 has no products or colors entered. As soon as D23 is entered as "Grape" "Orange" then the other formula's will correct.

Is there any way to have this work while still leaving gaps between rows?

*edit* I should add that my assumed intent as the color code -01, -02, -03 would add in the order the product/color was added so that if a product/color was added first on row 50 it would be assigned -01, then if another product/color was added on row 25 it would get color -02 as it was assigned second. Is this possible???

Let me know,

When I try to replicate what I think you mean above, mine works fine. I only get 01 for all colour codes if I enter a fruit on a row but do not allocate a colour to it. It's difficult to find the best way forward for that particular issue as I have no idea what the workbook will be used for or specifically HOW it will be used so I don't know if a product could be entered yet a colour not entered.

As for your edit, again without knowing how the workbook would be used, it's hard to second guess these things. For instance, is it highly likely that you would enter products and leave blank rows only to come back and fill them in later with more products?

If you need the colour codes added in the chronological order rather than a placement order then you will need to tackle this with a VBA approach rather than using a formula only approach.

Spencer101. Perhaps you can upload the worksheet again? Using the "unclejemima - FINAL (Hopefully!).xlsx" sheet from above, and the exact steps in my last post, I get the problem where 2 different colors get the same color code if i skip a cell. Not sure if my sheet is correct.

Please check out this vid I uploaded to youtube... http://www.youtube.com/watch?v=-Fsk_...ature=youtu.be

Watch it in 720p and you should be able to make out the codes.

At about 0:06 you can see 2 same codes for 2 different colors. At 0:13 when I add the Grape Orange, you can see the other codes correct themselves. Is this the correct behavior with the formula's?

When you say "chronological order rather than a placement order" placement order refers to the order they are in the spreadsheet. Your formula approach requires each formula to be one after the other, correct?

If I need them to be in Placement order, I have to go about it VBA way. Any idea how I would go about this? Should I start a new thread asking if anyone can create a VBA for this?

If I create Grape Green and Grape Red, I will probably print the codes and use them on product labels/tags. If then when I add Grape Orange, then it changes the other codes for Grape Green and Grape Red, this may cause problems if I've already printed tags for the other Grape Green and Grape Red. Just thinking of all the possible scenario's where this might cause confusion. Test and test again until I can't break it

Let me know, and thanks again!