filter multiple values

[Sunir M S]

I have a worksheet with the following details

Object Name Last Database Backup

NZ01_root 24/10/09 01:11:02
NZ01_netezza_data 24/10/09 05:43:14
AIX353_livebe_RMAN 24/10/09 01:06:05
AIX353_livech_RMAN 23/10/09 23:39:04

Now can i kind of create a code or template that would like have a fixed
list which has to be searched within the worksheet? For e.g in the above
example, say i have a report which publishes hundreds of object names, how do
i get the date and times of "Z01_root" and "NZ01_netezza_data" only, the
rest should be filtered out. i am talking about just keeping like 100 object
names out of a list of 500 object names. Note: there is no order, row/coloumn
for the initial report which has all the details.

 

[Bernie Deitrick]

Sunir,

I'm not sure what you want, but you can always use a column of helper cells that return TRUE or
FALSE depending on a criteria, if you criteria is very complex and cannot be solved using one of the
custom filters available.

Let's say that you have a list of names that you want to show - use a formula like this

=NOT(ISERROR(MATCH(NameValue,NameList,False)))

Like

=NOT(ISERROR(MATCH(A2,$H2:$H100,False)))

and copy down, then filter on that column of formulas.

HTH,
Bernie
MS Excel MVP

 

[Sunir M S]

Thanks a lot Bernie.

Howeevr considering my lack of experience with excel i couldnt get that
right..Let me be more simple this time.

NAME AGE SEX
Andre 25 m
Charles 26 m
Julia 22 f
dev 25 m

Now lez say this list continue to a few hunfred names and it gets published
every day. One more condition is that the name do not come in the same order
i.e andre specific to row #2 etc. they may be random.

Now i need to to search for julia and dev within this sheet only, i.e filter
out details of julia and dev only, the rest should be filtered out.

Hope i made some sense. Apologies for the ignorance. I am an engineer and i
din have the need to work on the wonderful tool called excel till now. Thanks
a lot in advance!!

 

[Gord Dibben]

Autofilter>Custom

Equals Julia OR equals dev


Gord Dibben MS Excel MVP

 

[Sunir M S]

But like i said i just piut in an example for 2 names to show up. I am
talking about 1000 total names and filtering out some 400 names. That is i
need to filter about 600 names. cutom filter gives me just 2 entities that
can be filtered

Thanks in advance

Sunir M S

 

[Bernie Deitrick]

Sunir,

What is the logic you use to decide whether to show a name or not?

Create a formula using that logic for each row, and make sure that it
returns TRUE or FALSE. Then filter on that column.

HTH,
Bernie
MS Excel MVP

 

[Sunir M S]

HI Bernie

Actually thers no logic other than string matches. I am a backup
administrator. I receive a report of about 1000 database backup job status at
the end of day with completed times and date.

Out of that i have some 400 critical backup jobs that i am interested in
which has to be filtered out and published and the rest should be removed.
What i do is a tedious task of deleting each job that is not critical one by
one to finally have a report with only the critical backups.

So if i have these critical backup names in a seperate worksheet, i juz
wanted to know as to how i could filter the main sheet with all the jobs say
1000 to just the one that i need which evidently would be on another
worksheet.

The only criteria for the filter would be to match the names of the job.

Note: the jobs in the main report with the 1000 jobs are not in any
particular order and changes every day.


Thanks in advance

Sunir

 

[Bernie Deitrick]

Sunir,

Well, then, that is your criteria. Create your list of critical jobs of interest (on perhaps,
another sheet named Critical Jobs), and use a formula like

=NOT(ISERROR(MATCH(CellValue,ListOfCriticalBackUpJobs,False)))

like

=NOT(ISERROR(MATCH(A2,'Critical Jobs'!$A$2:$A$400,False)))

and filter your list to show those that return TRUE.

 

[Sunir M S]

Thank s alot Bernie. Its perfect!!

Sunir,

Well, then, that is your criteria. Create your list of critical jobs of interest (on perhaps,
another sheet named Critical Jobs), and use a formula like

=NOT(ISERROR(MATCH(CellValue,ListOfCriticalBackUpJobs,False)))

like

=NOT(ISERROR(MATCH(A2,'Critical Jobs'!$A$2:$A$400,False)))

and filter your list to show those that return TRUE.

--
HTH,
Bernie
MS Excel MVP





.