How to filter merged cells to get more than the first row

[Guest]

I'm using Excel 2003. I want to be able to filter merged cells (that are in
one column, covering several rows) so that all the data associated with the
merged cell appears and not just the top line.

e.g. set up a table with two columns and an auto filter at the top. In the
left column put certain categories of data, for example say food types e.g.
fruit or meat, and then in the right column the actual food, so for fruit you
could have apples, banannas and pears. Now for presentation purposes I want
to have the left column merged so that the food category appears once
verically centered in the middle of the rows, but I want to be able to filter
this column to pick up all the data i.e. if I filter for fruit I want to get
apples, bananas and pears not just apples.

Help anyone?

 

[Guest]

You could try grouping your data. First, go to Data/Group and
Outline/Setting and uncheck Summary rows below detail and Summary columns to
right of detail.

Let's say for example your data looks like

A B
1 Apple
2 Fruit Oranges
3 Pears


Select rows 2 and 3. Then click Data/Group and Outline/Group (do this for
all of your groups). When you filter for Fruit, you will initially see Fruit
in Col A and Apple in Col B, but you can expand the group (by clicking the +
sign on the left side of the screen) and should see the rest of the group.

Not a one step solution, but the only other thing I can think of is to put a
helper column to the right of your data (Column C) and enter "Fruit" in C1:C3
and change the font to white (so it does not appear on screen or print) and
filter on this column.