meaningful part numbers 2

[RoarkS]

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

 

[EngineeringEric]

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

 

[MintJulep]

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

 

[RoarkS]

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.

 

[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.

 

[IDS]

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/

 

[IDS]

You might also find the VBA function described at the link below useful.

http://newtonexcelbach.wordpress.com/2010/03/01/stringing-more-than-two-words-together/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IFRs]

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.

 

[IDS]

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/

 

[onlyadrafter]

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

 

[onlyadrafter]

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

 

[BUGGAR]

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.