Filtering columns independently

[Youssef B.]

Hi,
I was wondering if anyone had any advice on filtering columns independently.
I have the following data:

A B

1 3
2 8
3 8
4 6
5 9

Basically, I would like to filter column A independently of column B.
I'd like to to filter column A to show only A4 (for example) and still be
able to filter column B by any criteria I like. Normally, If I filter column
A by A4, Excel only shows the value from B4 in the B column.
Any suggestions would be appreciated.

 

[Sean Timmons]

Unfortunately, filters will not allow you to see one row of one column along
with multiple rows of another column.

there may be other ways to perform what you are attempting. If you provide a
desired end result, we may be able to assit in getting you there.

 

[Gord Dibben]

The filtering on column A hides the rows in B also so no can do as you wish.

If using 2003 or newer you could turn column A and column B into two
distinct "lists" unser Data>List>Create List.

These can be filtered independently.


Gord Dibben MS Excel MVP

 

[Youssef B.]

Hi guys,
thanks for the input.

Gord, I feel youre getting close, but to be more specific, the following is
an example of what I would like to acheive:

A B C D
1 1
2 2
3 3 D3=A1*B1
4 4
5 5
6 6
7 7
8 8
9 9
10 10

This is a simple example using the multiplication table. I would like to
filter both columns A and B independently, so that my formula can be used to
determine any combination of A*B (ie. 5*6 or 3*9 or 7*1 etc...).
Is this possible with Excel, or am I wasting my time trying to figure this
out?

 

[Gord Dibben]

Create a list for A1:A11 with title "toplist" in A1

Create a list for A12:A21 with title "bottomlist"

In D1 enter this formula.

=SUBTOTAL(4,A1:A11)*SUBTOTAL(4,A12:A21)

This picks the MAX from each filtered list, which will be the only visible
number.

Filter top list for 7

Filter bottom list for 8 to return 56 in D1

Closest I can come right now.


Gord