Hi I have a need to filter a large (10k lines ) spreadsheet that has street addresses on it into a delivery route.
I have a need to link 20 plus filters together to achieve my goal.
It would also nice if I could then preserve the filters so that if in the future I needed to do this exercise and there had been changed to the address lists, people move or no longer qualify, then I would not need to rewrite the filters.
I can do one filter at a time ie Skydale Drive 50 to 54
Skyedale Drive 50 to 54
Becmead Drive 58 to 66 : 71 to 1 : 2 to 10
Napoleon Close 2 to 8 : 9 to 1
Becmead Drive 12 to 18
Rawei Place 2 to 10 5 to 1
Becmead Drive 22 to 56
Skyedale Drive 57 to 47
But I would like to string all the above together plus others, I understand that I may need to do for example three filters on the first of the Becmead Close lines.
I guess that this may involve macros, but at present, I am unsure what my next step is.
Thanks
Stuart
No need for macros. You need to use an Advanced Filter where the filter criteria is entered directly into the spreadsheet.
It’s not totally clear what your data set looks like, so I’ll just guess a bit. Let’s assume we have an address table across two columns, one with the header street name and one with the header house number, and we’re only interested in seeing Skydale Drive between 50 and 54 as well as Napoleon Close between 2 and 8.
First thing we need to do is somewhere else on the sheet (or on another sheet) to enter three new column headers for our filter criteria table: first street name, then house number (for the lower bound), and another one called house number (for the upper bound). Please note that these column header names must match the ones on your addresses!
Then, in the first row of our filter criteria table, we enter Napoleon Close under street name and in then >=2 under the first house number and <=8 under the second house number. Each of the criteria on this row is AND criteria, which means an address must meet all these criteria to pass through the filter.
In the next row we enter Skydale Drive and <=50 and >=54. These two rows of filter criteria are OR criteria, which means that an address will pass through the filter if it matches any of the two filter criteria rows.
Finally, we select the entire address table, then click Data → More Filters → Advanced Filter… and then select our filter criteria table as Read Filter Criteria From and press OK.
Hope this helps.
Thanks for your help
This old brain does not function as well as younger ones, most of the team looked at me a bit strange when I mentioned spreadsheeting with Lotus 123 the other day,
So on another sheet, I have set up four columns Labelled Street Even/Odd *Number * *Number
Under each Heading, I have done as you said Skyedale Street Even <=50 >=54 etc etc
I have 10 rows of data plus the head row
I then highlight all 11 rows Click on Data More Filters Advanced Filter then I get a popup which I guess I need to put the definition of this filter into, but how do I do this if I enter the range A2:D13 I get a message this range does not have a valid query as this is on Sheet2 I did the $sheet2 etc and still got the same response.
Thanks for your help
You can type in the range directly, but if that fails, try just dragging the selection box around it. It’s difficult to explain exactly how you need to click and drag to do this, so perhaps it’s easier to follow in this video tutorial.
Hi Thanks for the tips and the video link I have things working but I now have a further problem, The report I get is in alphabetic order, the spreadsheet I am filtering is in alphabetic order, what I need is the report to be in the order of the advanced filter lines ie Sykedale then Becmead then Becmead then Napolean then Becmead then Rewai then Becmead then Skydale ,not all of Becmead then Napolean thenRewai then all of Skyedale. You can see the break down in the question. Should I use Base?
Sounds like you just need to sort the addresses after you’ve filtered them. Perhaps it may be good to start a new question about that? I’ve never used Base.
Hi Thanks for your help, I have in the past used a system that would allow me to do what I need but I no longer have access so I am attempting to replicate the results that I could achieve.