Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>

  4. #4
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    2 Star Lounger
    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.

  7. #7
    2 Star Lounger
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •