|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 23rd, 2005, 09:21 PM
|
|||
|
|||
SORT MIXED DATA
I am trying to sort a spreadsheet which has part numbers as the main
description (ie: 500111, 500123, 500111A, 500111B, 500123A ...) I want the sort option to sort the part number column with it's associated data in numerical order including the letters (ie: 500111, 500111A, 500111B, 500123, 500123A ... ) Anyone know how I can achieve this goal? 
|
|
#2
March 23rd, 2005, 09:41 PM
|
|||
|
|||
|
If your alpha numeric part number is always 6digits number and the rest
alphabet(s) then insert a column next to the part list and trype =--(left(a1,6)) and copy it down Now sort based on this new column selecting all the columns "jem" wrote in message ... I am trying to sort a spreadsheet which has part numbers as the main description (ie: 500111, 500123, 500111A, 500111B, 500123A ...) I want the sort option to sort the part number column with it's associated data in numerical order including the letters (ie: 500111, 500111A, 500111B, 500123, 500123A ... ) Anyone know how I can achieve this goal?
|
|
#3
March 23rd, 2005, 09:51 PM
|
|||
|
|||
|
Use a helper column with the following formula:
=IF(ISERROR(A1*1),LEFT(A1,LEN(A1)-1),A1&"") where A1 contains the part #. Fill the formula down, select both columns, and sort ascending, first on the formula column, then on the part column. HTH Jason Atlanta, GA -----Original Message----- I am trying to sort a spreadsheet which has part numbers as the main description (ie: 500111, 500123, 500111A, 500111B, 500123A ...) I want the sort option to sort the part number column with it's associated data in numerical order including the letters (ie: 500111, 500111A, 500111B, 500123, 500123A ... ) Anyone know how I can achieve this goal? .
|
|
#4
March 23rd, 2005, 10:41 PM
|
|||
|
|||
|
you can use an @mid function to "strip off" the first 6
numeric characters and put into a new column, sort on this formula with a secondary sort on the alphanumeric part number to order the alpha numbers. -----Original Message----- I am trying to sort a spreadsheet which has part numbers as the main description (ie: 500111, 500123, 500111A, 500111B, 500123A ...) I want the sort option to sort the part number column with it's associated data in numerical order including the letters (ie: 500111, 500111A, 500111B, 500123, 500123A ... ) Anyone know how I can achieve this goal? .
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| How do I sort a column of data and have each data row sort accordi | Oedalis | General Discussion | 1 | March 17th, 2005 11:52 PM |
| Rapid input Via datasheet | RudyR_Seattle | General Discussion | 4 | January 31st, 2005 01:33 AM |
| Format on data to import to Access tables? (I need your advice) | Niklas Östergren | General Discussion | 5 | December 13th, 2004 02:54 PM |
| transpose | john | Using Forms | 1 | November 24th, 2004 06:16 PM |
| How to create graphs in a monthly report where the base data can change | John Clarke | Charts and Charting | 3 | June 25th, 2004 02:22 AM |
Linear Mode
