sorting with 2 relational coloms and blank spaces

[friso]

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:<table><tr><td valign=top>Infrastructure</Td><td>sub1

sub2

sub3</Td></tr><tr><td valign=top>Products</Td><td>sub1

sub2

sub3</Td></tr></Table>
if i however sort this data i get this:<table><tr><td valign=top>Infrastructure</Td><td>sub1</Td></tr><tr><td valign=top>Products</Td><td>sub2</Td></tr><tr><td valign=top></Td><td>sub3</Td></tr></Table>

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

 

[eliW]

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

 

[friso]

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

 

[eliW]

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

 

[Dave Hawley]

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.

 

[Mark W.]

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.