Results 1 to 7 of 7
Thread: Sorting in Excel (Excel 2000)

20051031, 16:40 #1
 Join Date
 Mar 2001
 Location
 Colorado, USA
 Posts
 144
 Thanks
 0
 Thanked 0 Times in 0 Posts
Sorting in Excel (Excel 2000)
I've got a requirements traceability matrix in a spreadsheet that I am using to sort the requirements based on one of four different documents. The requirements are in the form 1.2.3.4.5 [1] (where 1.2.3.4.5 is the section containing the requirement and [1] is the "shall", or specific requirement in the section. A section may have one or more "shalls" in it). In the spreadsheet, I've broken each requirement into it component pieces so that each number is in its own column in the spreadsheet (i.e. 1 is in column 1, 2 is in column 2, etc.)
Obviously, for shorter requirement numbers (like 1.2.3 [2]) there are empty cells between the last number of the requirement and the "shall" number (the number in brackets above). When I sort, Excel seems to handle these differently than a cell containing a zero, for example, and this causes a problem. For example, the requirement 3.3.9.3.3 [1] comes before 3.3.9.3 [1] in the sorted list when it should come after. Is there any way to get Excel to think of empty cells as containing zero without actually putting zeros in them?
FWIW, I'm tracking four documents, each containing requirements that can be up to six numbers long and a single shall number. This means that the spreadsheet uses 32 columns (including 3 spacer columns and a shall part column for one of the documents). If this has been done before and there is a more efficient way to so the sort, feel free to speak up.
Thanks for the help.

20051031, 16:53 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sorting in Excel (Excel 2000)
<img src=/S/confused.gif border=0 alt=confused width=15 height=20> When i sort in ascending order in excel I get that 3.3.9.3.3 [1] comes after 3.3.9.3 [1] since the space comes before the period.
Steve

20051031, 17:03 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Sorting in Excel (Excel 2000)
Bill means that he has put the parts in different columns, with the "shall" part in a final column. So you get something like this:
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>F</td><tr><td align=center>1</td><td align=right>3</td><td align=right>3</td><td align=right>9</td><td align=right>3</td><td align=right>

20051031, 17:06 #4
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sorting in Excel (Excel 2000)
What about filling the empties with zeroes, but hiding zeroes from view? (Tools, options, view tab)
Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20051031, 17:36 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sorting in Excel (Excel 2000)
If it is setup like hans suggests it is, why can't you create a column concatenating each row together into 1 cell and then just sort on this 1 column. You can leave them blanks, and it should be able to made to concatenate correctly...
Steve

20051031, 19:23 #6
 Join Date
 Mar 2001
 Location
 Colorado, USA
 Posts
 144
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sorting in Excel (Excel 2000)
This is effectively what I started with in Word and Adobe FrameMaker. The problem there is that requirement 10 for a given paragraph comes after requirement 1 instead of after requirement 9 as it should. In other words, it does an alphnumeric sort where the character position in the string becomes important. I tried inserting some hidden zeros in the Word strings, but it was still not quite right and became quite cumbersome to work with once I imported it into FrameMaker.
My plan is to get this spreadsheet to work properly and then just generate properly sorted text files that I can import.

20051031, 22:01 #7
 Join Date
 Nov 2001
 Location
 Toowoomba, Queensland, Australia
 Posts
 112
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sorting in Excel (Excel 2000)
Have you tried converting the values to text (=TEXT(value,format_text) ). TEXT(value,"00") will return 00 for a blank, 01 for 1 and 10 for 10. If you then concatenate the string should sort correctly even with an alpha sort.