INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

meaningful part numbers

meaningful part numbers

(OP)
Howdy all,

So here is the question: I have A LOT of variations of parts to make using the configurations design table in solidworks. For those not familiar, columns list feature dimensions and rows are each unique configuration of a part... this allows for easy creation of multiple sizes of a single part. kind of like making an entire wrench set with 1 part model, where all you have to do is make a list on a table of every nominal hex size, and it automatically generates a part model for every size wrench in the list.

So my part has 4 basic dimensions... called t, Ø, s, r. What I was asked to do is make part numbers for the different size parts that are meaningful based upon the dimensions.

in example, I have a part that is made from the dimensions t=.090 Ø=.250 s=.500 and r=.750. My boss would like the part number to be 0092505007500 or something of similar... if I could figure out how to get excel to take the 4 dimension values of .090,.250,.5,.75 and dump it out into a singe cell and show 0092505007500 I would be a happy camper.

If it would be easier to have a legend equal to certain numbers to simplify the part numbers kind of like a car VIN so it would be a 4 digit part number... the sequence would be "tØsr" and each value would be given a letter (or dash number for those familiar with an hardware)... and end up with a number like "a7d9" which would accurately describe an individual part.

Hopefully that makes sense, if not, maybe that's why I'm having a hard time figuring it out because I don't even know how to ask! lol.

Thanks ahead of time.

Roark

RE: meaningful part numbers

TRY this, i am going to use reference of imaginary cells, A,B,C,D. one for each of your dimensions. In cell E, place:
=A1 &B1 &C1 &D1

If you want to add a hyphen:
=A1 &"-" &B1 &C1 &D1

That is the simplest non-macro way of doing this. In my opinion. Unless i misunderstood your intent

RE: meaningful part numbers

Can you really have a part for every possible permutation of dimensions?

RE: meaningful part numbers

(OP)
EngineeringEric,

Right on! The thing I'm fighting now is that with .090 and .1875 being a dimensions I'm using I wanted to pull out the decimal point... so I multiplied each cell by 1000. only problem now is that I have a 16 digit code. I need to figure out the dash number thing.

MintJulep... yup. I just made 140 permutations of the same part, with 3 variable dimensions based on an equation driven excel sheet. see picture.

Check out the left hand column which names the configuration.

RE: meaningful part numbers

Make a table for each dimension.

First column is the dimension value. Second column is an arbitrary identified.

VLOOKUP() becomes your friend.

You can also use the tables to feed drop-downs in cells for each dimension to make a part number builder.

RE: meaningful part numbers

If you want a single letter or digit code for each dimension you can create four tables with the possible values listed in column 1 and the codes in column 2. Give the tables range names (say tlist, philist, slist, rlist). If your dimensions are in cells A1 to D1 the formula for the part code will then be:

=Vlookup(A1, tlist,2) & Vlookup(B1, philist,2) & Vlookup(C1, slist,2) & Vlookup(D1, rlist,2)

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: meaningful part numbers

If the dimension is measured in sixteenths, for instance 1/16, 1/4, 3/8, etc then use 1, 4, 6 for that segment of the part number. If the dimension is rounded to the nearest 0.05 then use 02 for 0.10 and 08 for 0.40, 13 for 0.65 etc. You could also use A, B, C for 1, 2, 3 sixteenths and have a part number that alternated with numbers and letters to make it easier to decode.

RE: meaningful part numbers

Quote (MintJulep)

Make a table for each dimension.

First column is the dimension value. Second column is an arbitrary identified.

VLOOKUP() becomes your friend.

You can also use the tables to feed drop-downs in cells for each dimension to make a part number builder.

Beat me by 3 minutes!

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: meaningful part numbers

Hello,

not sure what the exact result you require, but something on the lines of:

=TEXT(VALUE(A1*1000&B1*1000&C1*1000&D1*1000),"000000000000")

is this of any use?

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

RE: meaningful part numbers

Hello (again).

Sorry above formula not accurate

try

=TEXT((A1*1000),"000")&TEXT((B1*1000),"000")&TEXT((C1*1000),"000")&TEXT((D1*1000),"000")

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

RE: meaningful part numbers

Off-the-wall here:

I believe you can do this in AutoCAD by making a dynamic block with all of your variables. Make a table for it in ACAD and this should do it (?)

Back to spreadsheets.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close