Custom filtering has only two entries (and, or)? Ive got at least

Hello,

Ive got a problem with filtering. Ive got a sheet with at least 20 columns
and about 1000 rows. In column D there are all different activitys. I shall
give you an example what column D looks like:
0005
0005
0006
0006
0007
0007
0008
0008
0008
0008
0009
0009
0011
0011
0012
0012
0020
002B
002B
002B
002B
0030
003C
003C
0040
0050
005A
005A
0025
004C
004C
etc. etc.

When i want one or two activities its not a problem, i can use the filter
option and than go to custom. The problem is that i always need more than 2
activities.

Is it possible to create a macro that allows me to enter as much activities
as i want. After entering these activities (maybe in a popup??) Column D
shows these activities and ofcourse the whole line...

Please help me with this, its killing me!!!!

Thanks

TooN,

You need to use a helper column of formulas. For example, if you create a list of 'desired values'
using a named range Desired

The Helper formula is this, assuming your data start in row 2:

=NOT(ISERROR(MATCH(D2,Desired,FALSE)))

Then filter based on that column, for TRUE.

HTH,
Bernie
MS Excel MVP


"TooN" <TooN@discussions.microsoft.com> wrote in message
news:1D11327E-5E9F-4FAF-9191-C589868FD5C7@microsoft.com...
> Hello,
>
> Ive got a problem with filtering. Ive got a sheet with at least 20 columns
> and about 1000 rows. In column D there are all different activitys. I shall
> give you an example what column D looks like:
> 0005
> 0005
> 0006
> 0006
> 0007
> 0007
> 0008
> 0008
> 0008
> 0008
> 0009
> 0009
> 0011
> 0011
> 0012
> 0012
> 0020
> 002B
> 002B
> 002B
> 002B
> 0030
> 003C
> 003C
> 0040
> 0050
> 005A
> 005A
> 0025
> 004C
> 004C
> etc. etc.
>
> When i want one or two activities its not a problem, i can use the filter
> option and than go to custom. The problem is that i always need more than 2
> activities.
>
> Is it possible to create a macro that allows me to enter as much activities
> as i want. After entering these activities (maybe in a popup??) Column D
> shows these activities and ofcourse the whole line...
>
> Please help me with this, its killing me!!!!
>
> Thanks
>

Hi Bernie,

Thanks for the quick response but i dont know exactly what you mean. I tried
to use the formula but i couldnt get it to work, could you explain it to me
one more time?

Thanks,

TooN

"Bernie Deitrick" wrote:

> TooN,
>
> You need to use a helper column of formulas. For example, if you create a list of 'desired values'
> using a named range Desired
>
> The Helper formula is this, assuming your data start in row 2:
>
> =NOT(ISERROR(MATCH(D2,Desired,FALSE)))
>
> Then filter based on that column, for TRUE.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "TooN" <TooN@discussions.microsoft.com> wrote in message
> news:1D11327E-5E9F-4FAF-9191-C589868FD5C7@microsoft.com...
> > Hello,
> >
> > Ive got a problem with filtering. Ive got a sheet with at least 20 columns
> > and about 1000 rows. In column D there are all different activitys. I shall
> > give you an example what column D looks like:
> > 0005
> > 0005
> > 0006
> > 0006
> > 0007
> > 0007
> > 0008
> > 0008
> > 0008
> > 0008
> > 0009
> > 0009
> > 0011
> > 0011
> > 0012
> > 0012
> > 0020
> > 002B
> > 002B
> > 002B
> > 002B
> > 0030
> > 003C
> > 003C
> > 0040
> > 0050
> > 005A
> > 005A
> > 0025
> > 004C
> > 004C
> > etc. etc.
> >
> > When i want one or two activities its not a problem, i can use the filter
> > option and than go to custom. The problem is that i always need more than 2
> > activities.
> >
> > Is it possible to create a macro that allows me to enter as much activities
> > as i want. After entering these activities (maybe in a popup??) Column D
> > shows these activities and ofcourse the whole line...
> >
> > Please help me with this, its killing me!!!!
> >
> > Thanks
> >
>
>
>

Select, let's say, ten blank cells. Name then Desired either using the name box or Insert names.
Then place the values that you want to show into those cells. Look at help about named ranges if
you still have questions. Then use the formula that I gave you, assuming that the multiple values
that you want to show are in column D, starting in row 2. Copy the formula down to match your
database, and then filter on the column of formulas.

If you can't get it to work, contact me privately at deitbe at consumer dot org and I will send you
a working example.

HTH,
Bernie
MS Excel MVP


"TooN" <TooN@discussions.microsoft.com> wrote in message
news:D9554101-F007-4A64-A245-F5CF32416759@microsoft.com...
> Hi Bernie,
>
> Thanks for the quick response but i dont know exactly what you mean. I tried
> to use the formula but i couldnt get it to work, could you explain it to me
> one more time?
>
> Thanks,
>
> TooN
>
> "Bernie Deitrick" wrote:
>
>> TooN,
>>
>> You need to use a helper column of formulas. For example, if you create a list of 'desired
>> values'
>> using a named range Desired
>>
>> The Helper formula is this, assuming your data start in row 2:
>>
>> =NOT(ISERROR(MATCH(D2,Desired,FALSE)))
>>
>> Then filter based on that column, for TRUE.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "TooN" <TooN@discussions.microsoft.com> wrote in message
>> news:1D11327E-5E9F-4FAF-9191-C589868FD5C7@microsoft.com...
>> > Hello,
>> >
>> > Ive got a problem with filtering. Ive got a sheet with at least 20 columns
>> > and about 1000 rows. In column D there are all different activitys. I shall
>> > give you an example what column D looks like:
>> > 0005
>> > 0005
>> > 0006
>> > 0006
>> > 0007
>> > 0007
>> > 0008
>> > 0008
>> > 0008
>> > 0008
>> > 0009
>> > 0009
>> > 0011
>> > 0011
>> > 0012
>> > 0012
>> > 0020
>> > 002B
>> > 002B
>> > 002B
>> > 002B
>> > 0030
>> > 003C
>> > 003C
>> > 0040
>> > 0050
>> > 005A
>> > 005A
>> > 0025
>> > 004C
>> > 004C
>> > etc. etc.
>> >
>> > When i want one or two activities its not a problem, i can use the filter
>> > option and than go to custom. The problem is that i always need more than 2
>> > activities.
>> >
>> > Is it possible to create a macro that allows me to enter as much activities
>> > as i want. After entering these activities (maybe in a popup??) Column D
>> > shows these activities and ofcourse the whole line...
>> >
>> > Please help me with this, its killing me!!!!
>> >
>> > Thanks
>> >
>>
>>
>>