Filter/Select multiple rows

A

a_moron

Is this possible to do?

The result I want is to filter _ALL_ common (group of) values column
*CA* that has the value 1 in column *CB*
Code:
--------------------
CA CB
----------
A 0
A 0
A 1
A 0
B 1
C 0
C 0
C 0
D 0
D 0
D 1
E 1
E 1
F 0
F 0
F 0
F 0
--------------------
So the result would look something like this:
Code:
--------------------
CA CB
----------
C 0
C 0
C 0
F 0
F 0
F 0
F 0
 
B

Bernie Deitrick

You need to use a helper column of formulas: in CC2, for example, use one of these formulas

=SUMIF(CA:CA,CA2,CB:CB)=0

=SUMPRODUCT(($CA$2:$CA$1000=CA2*$CB$2:$CB$1000=1))=0

depending on whether you only have 1's and 0's or other numbers...

Then copy them down to match you column, and filter on CC for TRUE.

HTH,
Bernie
MS Excel MVP
 
R

Ron Coderre

Try this:
Make sure your data has column headings above the actual data.
Select your entire data list (including the headings)
Data>Filter>AutoFilter
Click the dropdown list in Col_CB
Select 0 <-zero (your example indicates you want zero values)

The list should now hide all rows where Col_CB does not = zero.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
R

Ron Coderre

Well, I completely misinterpreted what you are looking for:

Try this:
CD1: test
CD: =SUMPRODUCT(($CB$2:$CB$18=CB2)*$CC$2:$CC$18)=0

Select your data list CA1:CB1000
Data>Filter>Advanced Filter
List Range: (your already selected list)
Criteria Range: $CE$1:$CE$2
Click the [OK] button

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


Ron Coderre said:
Try this:
Make sure your data has column headings above the actual data.
Select your entire data list (including the headings)
Data>Filter>AutoFilter
Click the dropdown list in Col_CB
Select 0 <-zero (your example indicates you want zero values)

The list should now hide all rows where Col_CB does not = zero.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
Click to expand...
 
R

Ron Coderre

Correction:

THIS:
CD1: test
CD: =SUMPRODUCT(($CB$2:$CB$18=CB2)*$CC$2:$CC$18)=0

SHOULD BE:
CE1: test
CE2: =SUMPRODUCT(($CB$2:$CB$18=CB2)*$CC$2:$CC$18)=0

My appologies for the typos.

***********
Regards,
Ron

XL2002, WinXP-Pro


Ron Coderre said:
Well, I completely misinterpreted what you are looking for:

Try this:
CD1: test
CD: =SUMPRODUCT(($CB$2:$CB$18=CB2)*$CC$2:$CC$18)=0

Select your data list CA1:CB1000
Data>Filter>Advanced Filter
List Range: (your already selected list)
Criteria Range: $CE$1:$CE$2
Click the [OK] button

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
Click to expand...
 
Top