Part Number Generator

[Threshold]

Hi All,

So honestly not sure how to start something for like this...
I'd like to create a Part Number Generator for hardware, we have number system but what I would like to do is depending what I choose, my options become limited to certain things, then once I have chosen everything, it generates my part number.

An example would be something like for screws or washers:

TYPE: Screw, DIA:1/4, PITCH: 20, Material: Stainless Steel, Head Type: Flat Head, Drive Type: Phillips...ect...
Therefore a PN would be something like A25020SSFP (Letters and Numbers would be predefined in some way)

TYPE: Washer, DIA:1/4, Material: Stainless Steel, ID: .250, OD: .500...ect...
and therefore a PN for this would be something like B250SS250500

So depending on the type some options wouldn't be able to be chosen...

Not sure if I want to go VBA or Drop Down or if there is an entirely other option...

Thanks for any and all help!

 

[AFPathfinder]

So, you are trying to create a unique identifier, right? In your examples, it looks like you building the PNs using a combo of existing data and a serial autonumber-ish thing (not sure where some of the letters are coming from).

I'm guessing this is upon creating a new part in the log, right (one at a time)? Access uses a counting system to auto assign numbers as unique IDs and it's possible to implement in Excel, but you may need some VBA to ensure the integrity of the data. Do you have a process for handling duplicate data?

 

[Threshold]

Correct, it would be a unique indentifier. (The letters and stuff come from an existing data set that we have that is associated with the different types of stuff).

This would be upong creating a new part in the log (as mentioned by you, one at a time.)

I believe I can do the duplicate data in some way. As of right now, I'm not to concerned about that.

Thank you.

 

[AFPathfinder]

This may be a little complicated with so many part types and column values, but I would use concatenation and a little math and string manipulation to build the PN.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Unique ID
[/TD]
[TD]Type
[/TD]
[TD]Dia.
[/TD]
[TD]Pitch
[/TD]
[TD]Material
[/TD]
[TD]Head
[/TD]
[TD]Drive
[/TD]
[TD]ID
[/TD]
[TD]OD
[/TD]
[/TR]
[TR]
[TD]S25020SSFP00
[/TD]
[TD]Screw
[/TD]
[TD]0.25
[/TD]
[TD]20
[/TD]
[TD]SS
[/TD]
[TD]Flat
[/TD]
[TD]Phillips
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]W250SS250500
[/TD]
[TD]Washer
[/TD]
[TD]0.25
[/TD]
[TD][/TD]
[TD]SS
[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.25
[/TD]
[TD]0.5
[/TD]
[/TR]
</tbody>[/TABLE]

The "&" is used to join the values of two or more cells. I also used the function LEFT() to only use the first letter of some fields. On the fractions, I multiplied by 1000 to get whole numbers.

Here is the formula I used in column A to get the unique IDs:

=LEFT(B2,1)&C2*1000&D2&E2&LEFT(F2,1)&LEFT(G2,1)&H2*1000&I2*1000

This may not be exactly what you're looking for, but the thought behind it should get you closer to your solution. Once the formula is set, you can just use the fill handle to drag a formula down to new items that need a unique ID.

 

[Threshold]

This is perfect! I think from here I can work out most of what I need.

The only question is for any column that is left blank, is it possible to remove the *1000, this leads to 000 being in it's place instead if no value...

Again, thank you for your help! This is definitely what I needed. Didn't even think to use concatenation.

 

[AFPathfinder]

Hmmm, you could use IF statements, such as IF(H2="","",H2*1000) or IF(ISBLANK(H2),"",H2*1000) for those items, but it will extend out the formula (not a problem except it's harder to read if you need to adjust). I can think of some fancier solutions with macros, but that is probably much more work than required for what you're looking for.

 

[Threshold]

Thanks for the help! Truly Appreciate it!