Filter/Select multiple rows

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


"a_moron" <[email protected]> wrote in message
news:[email protected]...
>
> 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
> --------------------
>
>
> --
> a_moron
>
>
> ------------------------------------------------------------------------
> a_moron's Profile: http://www.excelforum.com/member.php...o&userid=30671
> View this thread: http://www.excelforum.com/showthread...hreadid=503300
>

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


"a_moron" wrote:

>
> 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
> --------------------
>
>
> --
> a_moron
>
>
> ------------------------------------------------------------------------
> a_moron's Profile: http://www.excelforum.com/member.php...o&userid=30671
> View this thread: http://www.excelforum.com/showthread...hreadid=503300
>
>

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" wrote:

> 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
>
>
> "a_moron" wrote:
>
> >
> > 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
> > --------------------
> >
> >
> > --
> > a_moron
> >
> >
> > ------------------------------------------------------------------------
> > a_moron's Profile: http://www.excelforum.com/member.php...o&userid=30671
> > View this thread: http://www.excelforum.com/showthread...hreadid=503300
> >
> >

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" wrote:

> 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" wrote:
>
> > 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
> >
> >
> > "a_moron" wrote:
> >
> > >
> > > 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
> > > --------------------
> > >
> > >
> > > --
> > > a_moron
> > >
> > >
> > > ------------------------------------------------------------------------
> > > a_moron's Profile: http://www.excelforum.com/member.php...o&userid=30671
> > > View this thread: http://www.excelforum.com/showthread...hreadid=503300
> > >
> > >