Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trouble with Advanced Filter
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 :D Thanks for listening, Ken McLennan Qld, Australia |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trouble with Advanced Filter
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" wrote in message ... 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 :D Thanks for listening, Ken McLennan Qld, Australia |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trouble with Advanced Filter
G'day there Bob,
You can find it he 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 Bob Phillips wrote: 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" wrote in message ... 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 :D Thanks for listening, Ken McLennan Qld, Australia |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trouble with Advanced Filter
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 dataControl+Shift+arrow down to select all rows to the endright clickDelete and then save the file it will shrink to about 110 KB -- Regards Roger Govier "ken" wrote in message ... G'day there Bob, You can find it he 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 Bob Phillips wrote: 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" wrote in message ... 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 :D Thanks for listening, Ken McLennan Qld, Australia __________ 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trouble with Advanced Filter
Sorry, the amended formula should, of course, be
=OFFSET(Data!$A$9,0,0,COUNTA(Data!$A$9:$A$65536),2 2) -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... 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 dataControl+Shift+arrow down to select all rows to the endright clickDelete and then save the file it will shrink to about 110 KB -- Regards Roger Govier "ken" wrote in message ... G'day there Bob, You can find it he 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 Bob Phillips wrote: 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" wrote in message ... 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 :D Thanks for listening, Ken McLennan Qld, Australia __________ 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 __________ Information from ESET Smart Security, version of virus signature database 4760 (20100111) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trouble with Advanced Filter
G'day there Roger,
Sorry, the amended formula should, of course, be =OFFSET(Data!$A$9,0,0,COUNTA(Data!$A$9:$A$65536),2 2) 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 :D Thanks again, See ya Ken |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trouble with Advanced Filter
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trouble with Advanced Filter
G'day there Herbert,
Excel 2007 Advanced Filter Manual and Macro: http://www.mediafire.com/file/0ywznh...01_11_10a.xlsm Thanks for that mate. Having a play with it at the moment. Take care, Ken Qld, Australia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
trouble with advanced filter | Excel Discussion (Misc queries) | |||
How do I use advanced filter to filter for blank cells? | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |