Trouble with Advanced Filter

[ken]

G'day there One & All,

After much swearing, cursing & experiment - all ineffective - I'm now
asking for assistance with my latest fiasco.

I have a list of data which includes dates, text, and numbers but none
combined, i.e. each column has only one of these types. There are 22
columns, but I need only about 12 as criteria. I've placed these above
the data list with a couple of rows separating them.

I have the data in a dynamic range labelled "Database", and the critera
in a static range which I've named "Criteria". As you can see, I have a
gift with inspired name assignments

To ensure the criteria names are identical to the data column titles I
have now applied a formula to each. These are simply "=A10" or whatever.
I initially copied & pasted the headings, but had to rename a few so I
did this in case I need to rename them again. Regardless, my filtering
didn't work in either case.

I have generated about 4 rows of dummy data of the same form that the
real data will be. I then copied these 3 times to give 12 rows of data.

The person's names are of the format 'SMITH A B'. Because of the
duplication there are 3 records with that field entry. I have used
'SMITH' in the appropriate criteria column and then tried filtering. I
found that this returned every record. Even those whose name is 'BROWN'
or whatever. I have tried copying to another location and filtering in
place, but with the same result each time. I've tried 'SMITH*';
'="=SMITH"'; '="=SMITH*"'; all with the same result - all records
returned. This was when I used the entire criteria range of 11 columns
with 3 rows, but only one criteria entered.

I then used only that criteria. That is, I used only the two cells
holding the heading & 'SMITH' as my criteria. This made a change, in
that it returned the first record only. It did have 'SMITH' as the name,
but the other records with this name were not returned.

I have experimented with different date formats, and returned either
all records or none. Even copy/pasting the date entry from the data to
the criteria hasn't worked.

I like to blame Microsoft for making faulty software, but I'm
inclined to think that I've mucked this up by myself. I've searched the
'net but not found anything to assist.

If anyone here has any ideas, please point them at me

Thanks for listening,
Ken McLennan
Qld, Australia

 

[Bob Phillips]

Ken,

It is a bit hard to debug without the data (needless to say, a simple test
works fine here). Can you post the workbook somewhere on a free fileshare
site?

HTH

Bob

 

[ken]

G'day there Bob,

You can find it here:

http://people.aapt.net.au/~AAPT_14660421/

It's not a fancy page, just a blank white expanse with a link to the XL
file but it worked here a minute ago. I should warn you though, that
it's over 2 Megs. I have no idea why, but I think it might be because I
formatted a couple of columns down to the bottom of the sheet since I
dunno how many entries there can be.

Thanks for helping, it's much appreciated.
Ken

 

[Roger Govier]

Hi Ken

Your problem is that your Database range does not include a Header row.
So Advanced filter, doesn't know what it is looking for.
Change from
=OFFSET(Data!$A$10,0,0,COUNTA(Data!$A$10:$A$65536),22)
to
=OFFSET(Data!$A$9,0,0,COUNTA(Data!$A$10:$A$65536),22)

By the way, if you select say row 25 on sheets data>Control+Shift+arrow down
to select all rows to the end>right click>Delete and then save the file it
will shrink to about 110 KB

--
Regards
Roger Govier

G'day there Bob,

You can find it here:

http://people.aapt.net.au/~AAPT_14660421/

It's not a fancy page, just a blank white expanse with a link to the XL
file but it worked here a minute ago. I should warn you though, that
it's over 2 Megs. I have no idea why, but I think it might be because I
formatted a couple of columns down to the bottom of the sheet since I
dunno how many entries there can be.

Thanks for helping, it's much appreciated.
Ken




__________ Information from ESET Smart Security, version of virus
signature database 4760 (20100111) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4760 (20100111) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Roger Govier]

Sorry, the amended formula should, of course, be

=OFFSET(Data!$A$9,0,0,COUNTA(Data!$A$9:$A$65536),22)

--
Regards
Roger Govier

Hi Ken

Your problem is that your Database range does not include a Header row.
So Advanced filter, doesn't know what it is looking for.
Change from
=OFFSET(Data!$A$10,0,0,COUNTA(Data!$A$10:$A$65536),22)
to
=OFFSET(Data!$A$9,0,0,COUNTA(Data!$A$10:$A$65536),22)

By the way, if you select say row 25 on sheets data>Control+Shift+arrow
down to select all rows to the end>right click>Delete and then save the
file it will shrink to about 110 KB

--
Regards
Roger Govier



__________ Information from ESET Smart Security, version of virus
signature database 4760 (20100111) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4760 (20100111) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Herbert Seidenberg]

Excel 2007 Advanced Filter
Manual and Macro:
http://www.mediafire.com/file/0ywznhzyyjj/01_11_10a.xlsm

 

[ken]

G'day there Roger,

Sorry, the amended formula should, of course, be

=OFFSET(Data!$A$9,0,0,COUNTA(Data!$A$9:$A$65536),22)

Thanks muchly for that Roger, it works like a charm now. Or at least,
just like an XL spreadsheet

I've also followed your advice about shrinking the file. Oddly enough,
the filter still didn't work properly until after I did the shrinky bit.
However it's fine now.

Thanks very much for all the help I received. It's much appreciated.
Now all I have to do is build the rest of the application

Thanks again,
See ya
Ken

 

[ken]

G'day there again, Roger (or anyone else reading this)

Now that it's working, I'd like to clarify something about Advanced
Filters.

Do I need to set the criteria range to only those cells that actually
contain data? Or can I leave blanks.

I have a range from A1 to K3 that contains the cells where the filter
criteria will be. Suppose that A1 contains a field header called NAME
with the surname "Smith" as the required filter in A2.

Can I still use Range("A1:K3") as my criteria in the Advanced Filter
dialog, or do I have to reduce it to just Range("A1:A2")?

I've found that the latter works and the former doesn't but I thought I
may have something else wrong.

Thanks for your help,
See ya
Ken
Qld, Australia

 

[ken]

G'day there Herbert,

Excel 2007 Advanced Filter
Manual and Macro:
http://www.mediafire.com/file/0ywznhzyyjj/01_11_10a.xlsm

Thanks for that mate. Having a play with it at the moment.

Take care,
Ken
Qld, Australia

 

[Bob Phillips]

I tried it with a very cut-down version, A1:B2 with only data in A2, not B2,
and it works fine for me

 

[ken]

G'day there Bob,

I tried it with a very cut-down version, A1:B2 with only data in A2, not B2,
and it works fine for me

Thanks for that. I spoke too soon before. It's still not working. I
found that when I run a filter with a criteria in it, say B1= "Name" &
B2="Smith" with the criteria range set to "Criteria" which is a named
range of A1:K3 then it will work sometimes. However once it has run
successfully and then reset via "Showall" and I try it again, the
Criteria Range field in the dialog doesn't contain "Criteria", it
contains B1:B2.

If I then change criteria by removing "Smith" and enter say "Somewhere"
under the Start_Loc heading and run the filter with "Criteria" in the
dialog, it still keeps B1:B2 as its criteria range complet with marching
ants around those cells. This is even though the criteria may now be
E1:E2.

Still working on it. I may have to reset using Criteria="" in the
Advanced Filter code plus dumping the bottom criteria row if there are
no Between dates to worry about. I'm not sure, but it must be in my
noggin somewhere

See ya
Ken