| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Sorting rows with alphanumeric starter cells
I have a worksheet with rows of information. The start cell of each row (in column A) has a part number which may or may not contain a suffix. The suffix is usually of the format "part#_1" or "part#AT" for example. In other words the same part number maybe repeated and I distinguish the repeats with an alpha suffix or underscore and numeral. The problem I'm having is sorting the sheet by column A, part#, and those part numbers with their suffixes. The suffixes cause the sort to list the all "number only" part# to be at the top followed by a separate list of the suffixed numbers in numerical order at the end. You have to remember to scroll to the end portion of the listing to see if their are any additonal part number variables to the originating one since the rest of the data in those rows may contain different information. How do I get the numbers to sort properly with the suffixed following their respective parent part#? -- axlmastr ------------------------------------------------------------------------ axlmastr's Profile: http://www.excelforum.com/member.php...o&userid=26880 View this thread: http://www.excelforum.com/showthread...hreadid=401136 |
|
#2
|
|||
|
|||
|
Is there a space between the alpha *prefix* and the actual part number
*suffix*? If there *IS*, you can try using Data - Text To Columns to create a "helper" column, and then sort on the helper. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "axlmastr" wrote in message ... I have a worksheet with rows of information. The start cell of each row (in column A) has a part number which may or may not contain a suffix. The suffix is usually of the format "part#_1" or "part#AT" for example. In other words the same part number maybe repeated and I distinguish the repeats with an alpha suffix or underscore and numeral. The problem I'm having is sorting the sheet by column A, part#, and those part numbers with their suffixes. The suffixes cause the sort to list the all "number only" part# to be at the top followed by a separate list of the suffixed numbers in numerical order at the end. You have to remember to scroll to the end portion of the listing to see if their are any additonal part number variables to the originating one since the rest of the data in those rows may contain different information. How do I get the numbers to sort properly with the suffixed following their respective parent part#? -- axlmastr ------------------------------------------------------------------------ axlmastr's Profile: http://www.excelforum.com/member.php...o&userid=26880 View this thread: http://www.excelforum.com/showthread...hreadid=401136 |
|
#3
|
|||
|
|||
|
@RagDyeR Here's and example of my list in column A: 7213 7214 7323AT 7323MT 7333AT 7333MT 7401 7401_2 7401_3 7402 7402_2 I want to sort the rows by the column and have the order you see above. I want the mixed use numbers (with suffixes) to be in the same order as the standard numeral type part numbers. Instead I get the mixed use numbers listed in order at the end of the numerical list. -- axlmastr ------------------------------------------------------------------------ axlmastr's Profile: http://www.excelforum.com/member.php...o&userid=26880 View this thread: http://www.excelforum.com/showthread...hreadid=401136 |
|
#4
|
|||
|
|||
|
Using TTC (Text To Columns), this is the best I could do:
Is it good enough? 7213 7214 7323AT 7323MT 7333AT 7333MT 7401_2 7401_3 7401 7402_2 7402 Let me know if it's good enough and I'll walk you through it. If it's not ... sorry! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "axlmastr" wrote in message ... @RagDyeR Here's and example of my list in column A: 7213 7214 7323AT 7323MT 7333AT 7333MT 7401 7401_2 7401_3 7402 7402_2 I want to sort the rows by the column and have the order you see above. I want the mixed use numbers (with suffixes) to be in the same order as the standard numeral type part numbers. Instead I get the mixed use numbers listed in order at the end of the numerical list. -- axlmastr ------------------------------------------------------------------------ axlmastr's Profile: http://www.excelforum.com/member.php...o&userid=26880 View this thread: http://www.excelforum.com/showthread...hreadid=401136 |
|
#5
|
|||
|
|||
|
RagDyer Wrote: Using TTC (Text To Columns), this is the best I could do: Is it good enough? 7213 7214 7323AT 7323MT 7333AT 7333MT 7401_2 7401_3 7401 7402_2 7402 Ya it's better than I have now. I see that the underscore places first, but that is a mild compromise for what you have acheived otherwise. Please advise, Thanks -- axlmastr ------------------------------------------------------------------------ axlmastr's Profile: http://www.excelforum.com/member.php...o&userid=26880 View this thread: http://www.excelforum.com/showthread...hreadid=401136 |
|
#6
|
|||
|
|||
|
Say your column of data is Column A.
You'll need 2 empty "helper" columns adjacent to A, so make sure B and C are empty, OR, insert 2 columns temporarily. Select the column of data, then: <Data <TTC Clicked "Fixed Width", then <Next Click in the "Data Preview" window to create a "break line", and drag it to separate the 4 digits from the rest. Then <Next You now see how TTC will separate the columns. The default location in the "Destination" window is your original column of data. This means TTC will *replace* your original data with the revised, separated data. You *don't* want this, so change the location in the destination window to B1. This tells TTC to retain the original column and start entering the revised data into Column B. Now, click <Finish You now have Columns A, B, and C, where A is the original data, B is the 4 digits, which are now *true numbers*, so they'll sort correctly as you want, and C, which is text. Select *all* 3 columns and <Data <Sort Set first sort key to Column B, ascending, and the second to Column C, ascending, then <OK. You should now have what I showed you. You can, of course, now delete the "helper" columns. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "axlmastr" wrote in message ... RagDyer Wrote: Using TTC (Text To Columns), this is the best I could do: Is it good enough? 7213 7214 7323AT 7323MT 7333AT 7333MT 7401_2 7401_3 7401 7402_2 7402 Ya it's better than I have now. I see that the underscore places first, but that is a mild compromise for what you have acheived otherwise. Please advise, Thanks -- axlmastr ------------------------------------------------------------------------ axlmastr's Profile: http://www.excelforum.com/member.php...o&userid=26880 View this thread: http://www.excelforum.com/showthread...hreadid=401136 |
|
#7
|
|||
|
|||
|
@RagDyer Thanks for the influencial info. I have just produced a test run of you instructions line for line. I am at a slight impass. I need to mention that I have data in cells across each row that one of these part numbers represents. Basically, to save myself writing a database in Access, I use the worksheet as a Bill Of Material. I have structured column headings from A-M. Each part number has it's own row, except for those that repeat due to their difference in components. The easiest way I find to distinguish items with the same Part Numbers but different components is by codes I add as suffixes. I can sort the three columns using you method, though I still see the true numbers in column B with the text in column C. How do I delete the helper cells (as you suggest) and not lose the data (true numbers and/or text) in them and still retain my original starter column with respect to the resulting sort? Also how do I include the remaining columns in the sort? Until now I selected the whole sheet and sorted by Column A and it worked ok, but I really like the results you showed me and would rather use your method if possible. Or can you tell me a better way to enter the part numbers in order to sort more cleanly? Thanks RagDyer -- axlmastr ------------------------------------------------------------------------ axlmastr's Profile: http://www.excelforum.com/member.php...o&userid=26880 View this thread: http://www.excelforum.com/showthread...hreadid=401136 |
|
#8
|
|||
|
|||
|
If I understand what you're saying, you've *inserted* a new Column B and C
to accept the parsed, separated data from Column A. So now, just do what you previously did, and select your "whole" sheet, and instead of sorting on Column A, just sort on Column B & C, and all the associated data in the adjoining columns will follow. And, there's really nothing that says that you must delete the new B & C. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "axlmastr" wrote in message ... @RagDyer Thanks for the influencial info. I have just produced a test run of you instructions line for line. I am at a slight impass. I need to mention that I have data in cells across each row that one of these part numbers represents. Basically, to save myself writing a database in Access, I use the worksheet as a Bill Of Material. I have structured column headings from A-M. Each part number has it's own row, except for those that repeat due to their difference in components. The easiest way I find to distinguish items with the same Part Numbers but different components is by codes I add as suffixes. I can sort the three columns using you method, though I still see the true numbers in column B with the text in column C. How do I delete the helper cells (as you suggest) and not lose the data (true numbers and/or text) in them and still retain my original starter column with respect to the resulting sort? Also how do I include the remaining columns in the sort? Until now I selected the whole sheet and sorted by Column A and it worked ok, but I really like the results you showed me and would rather use your method if possible. Or can you tell me a better way to enter the part numbers in order to sort more cleanly? Thanks RagDyer -- axlmastr ------------------------------------------------------------------------ axlmastr's Profile: http://www.excelforum.com/member.php...o&userid=26880 View this thread: http://www.excelforum.com/showthread...hreadid=401136 |
|
#9
|
|||
|
|||
|
axlmastr,
The Excel addin "Refinate" will do the kinds of things with BOM that you want to do and much more. It will save you lots of time as you get everything done just by clicking the mouse. It is designed specifically to work with bill of materials and assembly lists. It also processes net lists so that you can verify design changes and compare differences to older versions of the netlist. To sort the way you describe, drag to select all of your table and use Refinate's "one-row-per-part". This automatically converts the part# to text and they will sort by text rules. A bonus result of this process is that table formatting is applied and much of your print page setup is done automatically. www.refinate.com --Brian Taylor AnalogDigital Engineering axlmastr wrote: I have a worksheet with rows of information. The start cell of each row (in column A) has a part number which may or may not contain a suffix. The suffix is usually of the format "part#_1" or "part#AT" for example. In other words the same part number maybe repeated and I distinguish the repeats with an alpha suffix or underscore and numeral. The problem I'm having is sorting the sheet by column A, part#, and those part numbers with their suffixes. The suffixes cause the sort to list the all "number only" part# to be at the top followed by a separate list of the suffixed numbers in numerical order at the end. You have to remember to scroll to the end portion of the listing to see if their are any additonal part number variables to the originating one since the rest of the data in those rows may contain different information. How do I get the numbers to sort properly with the suffixed following their respective parent part#? -- axlmastr ------------------------------------------------------------------------ axlmastr's Profile: http://www.excelforum.com/member.php...o&userid=26880 View this thread: http://www.excelforum.com/showthread...hreadid=401136 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Realtime Automatic sorting of data in rows in new work sheet | Excel Worksheet Functions | |||
| I Need to divide all cells in rows 2 and 3 by 100 | Excel Discussion (Misc queries) | |||
| Sorting referenced cells | New Users to Excel | |||
| sorting detail rows - summary row in an outline | Excel Discussion (Misc queries) | |||
| Counting rows based on criteria in multiple cells | Excel Discussion (Misc queries) | |||
Linear Mode
