Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
decimal sorting
Hi,
I have a spreadsheet with an inventory list. The inventory items all have number associated with them (i.e. 1900.04). However Some of the inventory has multiple items associate with the same originating number (i.e. 1900.04-15). Is there a way to sort it so that all the similar numbers stay together (i.e. 1900.04, 1900.05, 1900.04-15) without sending the hyphenated values to the bottom of the list after 2000 for example. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
decimal sorting
It sounds like 1900.04 is numeric and 1900.04-05 is text.
You could make sure that you entered all the items as text by preformatting the column as Text or by entering your data with a leading apostrophe: '1900-04 Or you could use a helper column: =""&a2 If you concatenate your 1900.04 number with the empty string, the result will be a string. Then you can drag that formula down and sort by that column. Tamesh wrote: Hi, I have a spreadsheet with an inventory list. The inventory items all have number associated with them (i.e. 1900.04). However Some of the inventory has multiple items associate with the same originating number (i.e. 1900.04-15). Is there a way to sort it so that all the similar numbers stay together (i.e. 1900.04, 1900.05, 1900.04-15) without sending the hyphenated values to the bottom of the list after 2000 for example. Thanks. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
decimal sorting
hmm i'm afraid that neither one of those processes worked :-(. Below is a
snipped of my number set so you see what i'm trying to sort. Thanks. 1999.01 1999.02 1999.03 1999.04 1999.05 1999.06 1999.07 1999.19 1999.2 1999.21 1999.22 1999.23 2003.01 2003.02 2003.03 2003.04 2003.05 2003.06 2005.14 2005.15 1999.15.01-40 1999.28.01-06 1999.29.01-76 2003.17.01-02 2004.08.01-23 2004.12.01-02 2004.14.01-02 2004.16.a-b |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
decimal sorting
I put your data in A1:A28 and put this formula in B1:
=""&a1 and dragged down. Then I selected my range and sorted by column B. I ended up with data in this order: Column A---- Column B------ 1999.01 1999.01 1999.02 1999.02 1999.03 1999.03 1999.04 1999.04 1999.05 1999.05 1999.06 1999.06 1999.07 1999.07 1999.15.01-40 1999.15.01-40 1999.19 1999.19 1999.2 1999.2 1999.21 1999.21 1999.22 1999.22 1999.23 1999.23 1999.28.01-06 1999.28.01-06 1999.29.01-76 1999.29.01-76 2003.01 2003.01 2003.02 2003.02 2003.03 2003.03 2003.04 2003.04 2003.05 2003.05 2003.06 2003.06 2003.17.01-02 2003.17.01-02 2004.08.01-23 2004.08.01-23 2004.12.01-02 2004.12.01-02 2004.14.01-02 2004.14.01-02 2004.16.a-b 2004.16.a-b 2005.14 2005.14 2005.15 2005.15 It looked in order to me. Tamesh wrote: hmm i'm afraid that neither one of those processes worked :-(. Below is a snipped of my number set so you see what i'm trying to sort. Thanks. 1999.01 1999.02 1999.03 1999.04 1999.05 1999.06 1999.07 1999.19 1999.2 1999.21 1999.22 1999.23 2003.01 2003.02 2003.03 2003.04 2003.05 2003.06 2005.14 2005.15 1999.15.01-40 1999.28.01-06 1999.29.01-76 2003.17.01-02 2004.08.01-23 2004.12.01-02 2004.14.01-02 2004.16.a-b -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for: Format Decimal places? | Excel Discussion (Misc queries) | |||
Excel adds phantom decimal places: why? | Excel Discussion (Misc queries) | |||
sorting numbers more than one decimal | Excel Worksheet Functions | |||
How do I override fixed decimal place settings in EXcel 2003? | Excel Worksheet Functions | |||
decimal point override does not work | Excel Discussion (Misc queries) |