Advanced Filter

omegoku

New Member
Joined
May 15, 2006
Messages
38
Hi
I searched for Advanced Filter threads and none have answered my problem
Ive looked up Microsoft Office Assistant and followed the steps but it doesn't seem to be working.

I have a file containing rows of data, one of the columns contains the owners name. Seperatly (on a different sheet) I have a column of names.
I want to use advance filter to go through the main datasheet and pull out the ones that have an owner name that appears in the list of names.

When I tried an AutoFilter it allowed me to enter 2 names and the filter worked.
I tried setting up an Advanced filter but it returns the Column headers (owner name, company name, etc) and all the rows. So no filtering is being done.

So my question is, what exactly goes into the 3 range boxes? Do you include the headings in the List Range and Criteria Range? In Criteria range do I just include the column being checked against (in this case the list of names) or do I need to do something else?

Thanks for any input
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
The Criteria range must include the column heading and it must be identical to the column heading in the data range. In the data range include all the data plus the column heading. In the criteria range include all the criteria names and the heading.
 

omegoku

New Member
Joined
May 15, 2006
Messages
38
Thanks for the reply

I included all the data and heading in both List range and criteria range
Both lists have identical headings
It returns an unfiltered table

My test criteria is ="=john Doe" and ="=mary Doe" written in G2 and G3 respectivly. The criteria 'table' is blank apart from the headers and these 2 values.

Any ideas?
Thanks again
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Remove the quotes and the =.
 

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818

ADVERTISEMENT

Yes so in the criteria list you would have

john Doe
mary Doe

as simple strings
 

omegoku

New Member
Joined
May 15, 2006
Messages
38
No difference, I get exactly the same output with no filtering.
I must be missing something simple

in my sheet3 line 1 has the headers for criteria
in one of the columns underneath the owner name heading i have the list of names as simple strings
In my Sheet RawData row 2 has the exact same headers
and underneath that is the 40 or so lines of test data

I try copy the filtered data to the sheet under the criteria but it doesn't filter at all, just copies what I have.
Anyone have any ideas on what I may have missed?

Thanks
 

omegoku

New Member
Joined
May 15, 2006
Messages
38
I resolved the issue, thanks for your help

The error seemed to be as al result of me including empty rows in my criteria selection.

Thanks again
 

Forum statistics

Threads
1,136,878
Messages
5,678,324
Members
419,756
Latest member
vincent86kapelski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top