Search against separate excel file for existing values, adding next unique identifier

Hello all,

Hoping for some help with adding unique identifiers to a list of... let's call them products. I have attached an excel sheet showing how the input would look and the (hopefully) output. Basically, I would have a long list of these products with a "base name" which is added upon each time the product is used. Think of it as generations.

In my example, we have three different products that each has an associated quantity. I want the code to look in the Qty column (in this case B) and expand each record by the number indicated and add a unique identifier to the end. The catch is, I also need it to search a list of existing products stored in a separate file to determine if that product has been used before, and if so, what the next unique identifying number would be in the sequence. It would also be nice to be able to dictate what the unique identifier looks like (i.e. maybe it's not always just numerical, maybe we add a symbol after the number) though this is not strictly necessary.

eg: NAT214-005-006 has already been used 7 times before, so in the master list NAT214-005-006-00X (x=1-7), etc. already exists so the program should be able to recognize this and select NAT214-005-006-008 at which to start numbering.

Basically 3 separate steps
1) expand the records according to the values in the Qty column, copying the "base name" into each new row.
2) search the other excel file with the master list for this base name, and upon finding records, append the unique identifier from -001 to -00X (whatever is next in the sequence).
3) add these new products to the master list so it remains updated.


Thank you so much to whoever can help me out with this little problem.

Can you share the second file so that the code can reference this other file. There is no master list in the sample file.

Ah, yes, thanks. I have attached a sample master list as it might look for this example.

Here is a solution for you. Run the code from the "Sample Excel Sheet Naming" workbook, and you will be prompted to select the "MasterList" Workbook. Let us know how it goes.

Please Login or Register  to view this content.

Hi there,

Wow! That's fantastic! Thank you so much, it works quite well, I hope I'll be able to figure out how to adjust it for different columns etc. (we will be using it with a different header set up etc.). Just a couple adjustments/questions, would there be a way to automatically specify the master file? It's good as is right now as we still need to establish the file but once done, it would be nice to not have to select each time. Also, when I run the macro, the master list changes to read-only, is there a way to change this?

Thank you

This updated code will not open the master file as read only. The masterList.xlsx file will now open if it is located on the same path as the core file.

Please Login or Register  to view this content.

Hi maniacb,

Thanks so much for your help thus far, you are a real life saver. This code was great for some of our entries, however, now that I am trying to implement
it on the actual (rather large) list of plant names, there are instances where it doesn't work. I think I am running into some problems where the product
name has '$' symbols in it. I'm thinking that the code can't recognize these symbols since when I try to run it on plant names with $ symbols it doesn't
work. Unless it has something to do with the number of digits. Before I was involved in this, there was no consistency with the number of digits added
(i.e. people would add -1 rather than -001). I have included a sample of some of the problematic names in the master list I am now working with. Is it
possible to just ignore the '$' and just look at numbers?

Thanks again. I'm curious whether the lack of 000 formatting could cause a runtime error '5'. The macro was working well, but now can't seem to get past the bolded line below. If stuff in the master file doesn't match this format, would that cause this error? If so I may be able to remove them, but it would be nice to be able to add some code to simply skip over thing that don't follow the "-000" format.
For i = 3 To lr1
For j = 2 To lrm
lent = Len(wsm.Cells(j, 1).Value)
If ws1.Cells(i, 1).Value = Left(wsm.Cells(j, 1).Value, lent - 4) Then
exist = True
ws1.Cells(i, 3).Value = "exist"
ws1.Cells(i, 4).Value = Right(wsm.Cells(j, 1).Value, 3)
End If
Next j
Next i


i was also wondering if it is possible to add a column to the master list that saves the date the entry was added, it would be nice to see when things were added into the master.

Thanks again for your help with this.

Here is an update that will place the date next to any added number in the master list.

As for

I'm curious whether the lack of 000 formatting could cause a runtime error '5'. The macro was working well, but now can't seem to get past the bolded line below. If stuff in the master file doesn't match this format, would that cause this error?

Yes, if items in master file don't have the -000 format that error will occur. I will need to see some example data to work out a process to skip those items.

Please Login or Register  to view this content.

Yes, once file saved then becomes permanent record.

As for the second paragraph, that is correct.

You can disregard duplicates and gaps in the numbers, no issue.

That is simply fantastic. So it always goes to the highest number in the list and adds one? That's great, that way I can just add lists without worrying if they have already been put in previously.

I just found in error in the updated code. The sequence numbers weren't incrementing correctly.

Also, although duplicates and gaps are ok, the product numbers must be sorted with the largest number last, just like the example data. The code looks at the last item and takes the end -### number to then add new ones. So if you add new lists, please sort on product number before running code. The sort can be added into the code if you want.

Please Login or Register  to view this content.

Hello,

Yes an auto sort in the code would be very useful if this is required for the macro to run properly. The date added column should preserve the order of the entries if that is needed in future but I expect to never have to edit this list once the system is set. If you would be so kind as to add this function to the corrected code, I'll update everything and I won't bother you again (hopefully).

I wonder how I can ever repay you for being so generous with your time and skill?

Cheers

Here is the code with the sort built in:

Please Login or Register  to view this content.

Your thanks and appreciation is all I need.