Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
PJ
 
Posts: n/a
Default How do I format a cell for a custom part number?

Using Excel 2003;

I am trying to format a range of cells to accept hardware dimensions so I
can sort them by product code, diameter, pitch, and length. Since I can only
sort 3 columns, it would be helpful to combine pitch and diameter in a custom
number format.
Ideally I want to be able to enter diameter and pitch in the same cell as
follows:
"(diameter in whole numbers or fractions)-(pitch in whole numbers)"

For example, here are some common sizes:
10-24
10-32
1/4-20
5/16-18

I know how to get to the custom number format, but I don't understand how to
use the number codes to create the custom number format I need.
  #2   Report Post  
Dave O
 
Posts: n/a
Default

Hi, PJ-
Before you get too wrapped up in a workaround, may I offer a slightly
different solution? Your note says "Since I can only sort 3 columns"
which leads me to believe you'd prefer to be able to sort on more
columns than that. You can, if you insert a new column (which can
later be hidden from view) that concatenates the values of several
cells.

For instance: you have data laid out in columns A thru G, and you want
to be able to sort on every column. You can do this by adding a new
column I: on each row the formula in I is
=A1&B1&C1&D1&E1&F1&G1
.... which takes the entry from each cell and jams it together into one
value. You can then sort on column I, which is effectively the same as
sorting on each of the seven columns. You can then hide this column if
necessary, or leave it off your printed output.

Does this provide any increase in data precision? Or alleviate the
need to format something so it's real pretty?

Dave O

  #3   Report Post  
Walt Weber
 
Posts: n/a
Default

Hi PJ,

If you truly want a sort by diameter included, you should
probably convert all diameters to decimal values (Perhaps
another inserted column with lookups against a conversion
table). Otherwise, the screw samples you listed (10
guage, 1/4", and 5/16") will sort with 10 guage listing as
bigger than a quarter of an inch when it isn't.

Best Regards,
Walt


-----Original Message-----
Hi, PJ-
Before you get too wrapped up in a workaround, may I

offer a slightly
different solution? Your note says "Since I can only

sort 3 columns"
which leads me to believe you'd prefer to be able to sort

on more
columns than that. You can, if you insert a new column

(which can
later be hidden from view) that concatenates the values

of several
cells.

For instance: you have data laid out in columns A thru G,

and you want
to be able to sort on every column. You can do this by

adding a new
column I: on each row the formula in I is
=A1&B1&C1&D1&E1&F1&G1
.... which takes the entry from each cell and jams it

together into one
value. You can then sort on column I, which is

effectively the same as
sorting on each of the seven columns. You can then hide

this column if
necessary, or leave it off your printed output.

Does this provide any increase in data precision? Or

alleviate the
need to format something so it's real pretty?

Dave O

.

  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

You can only sort on 3 columns at a time, but you can do multiple sorts.

Sort in ascending order in groups of 3 fields at a time. Excel will remember
the previous sort and not damage those results.

PJ wrote:

Using Excel 2003;

I am trying to format a range of cells to accept hardware dimensions so I
can sort them by product code, diameter, pitch, and length. Since I can only
sort 3 columns, it would be helpful to combine pitch and diameter in a custom
number format.
Ideally I want to be able to enter diameter and pitch in the same cell as
follows:
"(diameter in whole numbers or fractions)-(pitch in whole numbers)"

For example, here are some common sizes:
10-24
10-32
1/4-20
5/16-18

I know how to get to the custom number format, but I don't understand how to
use the number codes to create the custom number format I need.


--

Dave Peterson
  #5   Report Post  
PJ
 
Posts: n/a
Default

Thanks Dave, I believe that is what I am looking for. I'm trying to clean up
orders by classifying the parts ordered, and avoid doing multiple sorts.

"Dave O" wrote:

Hi, PJ-
Before you get too wrapped up in a workaround, may I offer a slightly
different solution? Your note says "Since I can only sort 3 columns"
which leads me to believe you'd prefer to be able to sort on more
columns than that. You can, if you insert a new column (which can
later be hidden from view) that concatenates the values of several
cells.

For instance: you have data laid out in columns A thru G, and you want
to be able to sort on every column. You can do this by adding a new
column I: on each row the formula in I is
=A1&B1&C1&D1&E1&F1&G1
.... which takes the entry from each cell and jams it together into one
value. You can then sort on column I, which is effectively the same as
sorting on each of the seven columns. You can then hide this column if
necessary, or leave it off your printed output.

Does this provide any increase in data precision? Or alleviate the
need to format something so it's real pretty?

Dave O


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Custom Format for ¢ Stephen POWELL Excel Discussion (Misc queries) 0 February 25th 05 08:11 PM
Number is in a text format dbl Excel Worksheet Functions 1 February 19th 05 09:57 PM
Format the Cell into a telephone number but using country code Chris Quinn Excel Discussion (Misc queries) 1 January 27th 05 04:51 PM
How do I unhide the cell format function in Excel 2000 Len Melcer Excel Worksheet Functions 2 December 15th 04 07:49 PM
Date on two lines using a custom cell format possible? .:mmac:. Excel Discussion (Misc queries) 5 December 4th 04 10:41 PM


All times are GMT +1. The time now is 09:08 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"