Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: sorting with 2 relational coloms and blank spaces

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello,

    I have a problem with sorting. My sheet is set up so that colom 1 displays a general domain to which subdomains belong that are listed in colom 2. Like this:









    Infrastructure sub1

    sub2

    sub3
    Products sub1

    sub2

    sub3

    if i however sort this data i get this:













    Infrastructure sub1
    Products sub2
    sub3


    Questions: How do i get excel to retain the blank cells in colom 1? And how do i prevent sub1 to sub3 to be consolidated (when using consolidate function), for the subdomain names are usually the same but belong to different domains.

    Anyone gort an answer for this???


    [ This Message was edited by: friso on 2002-04-02 00:24 ]

    [ This Message was edited by: friso on 2002-04-02 00:28 ]

    [ This Message was edited by: friso on 2002-04-02 00:30 ]

  2. #2
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If I understand your problem correctly then you can solve it by filling the blank cells in column A with the corresponding domains.
    It can be done at once by selecting the desired range, then Edit | Goto | Special | blank, which selects all blank cells in the range. In the active cell you enter formula = (and point to the cell above), This would fill all the blanks with the content of the domains.
    Then yuo select column A and B and sort on column A then B.
    Eli

    [ This Message was edited by: eliW on 2002-04-02 03:04 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Eli, however I need those cells to be left blank for that is the desired format.

    [ This Message was edited by: friso on 2002-04-02 02:49 ]

  4. #4
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can add a coloumn identical to column A and fill it, sort on it and then hide or delete it.
    Eli

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi friso


    Not sure if this will do for your situation but Excel will not sort hidden rows. So select the Column, push F5, click "Special" then select blanks, click OK. No go to Format>Row>Hide.



  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-02 02:48, friso wrote:
    Thanks Eli, however I need those cells to be left blank for that is the desired format.

    [ This Message was edited by: friso on 2002-04-02 02:49 ]
    Instead of leaving them blank use a conditional format that changes the font color of repeated values to match the cell background pattern. It will appear that the repeating values aren't there, but since they are you can properly sort the list.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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