Ordering Results from a Filter in Calc or in Base

[stuarts.burgers]

I have a dataset in a Calc Sheet. I want to be able to filter so that I get the results in a very specific order.
What I have is Street Addresses and I have a “Mailman’s Route”. What I want to do is to filter or in some way order the Street Addresses so that they follow the " Mailman’s Route"
Included in the data set I have is the names of householders and what I am attempting to set up is a Spreadsheet to run through a Mail Merge program so that we create Addressed communication to the Householders.
As the “letters” are produced they are done in delivery order ie up one side of a cul de sac and down the other.
The Cul de Sac I can deal with it is the Street that the Cul de Sac runs off is the problem. I need to insert the Cul de Sac at the point on the spreadsheet so as you have a letter for the last house before the junction, then letters for the Cul de Sac, then a letter for the first house after the junction.

I know that it is possible as I have in the past used software to do it but I now no longer have access to this program.

Can some nice person help? I have attempted to use advanced filters in Calc but the result just creates a list in alphabetical order and groups all of one streets addresses together

Further to Jim K’s comment here are the files that I mentioned below along with the Mailman’s Route

After Advance Filtering.ods

DESIRED RESULT.ods

MAILMAN’S ROUTE.odt

I hope that this adds clarity. I have removed a lot of extra information from the spreadsheets as it is not related to my query.

[jimk]

The question is not precise enough to answer. Please give complete example data and desired results. See guidelines for asking.

[stuarts.burgers]

Hi, Jim thanks for your pointers I have created a couple of spreadsheets that show where I start and what I would like to finish with. They are about 280 line each but give a flavour of what I need, but how do I link to them.
This is a whole learning exercise for and I hope that as I learn others may as well and in the future, I will be able to pay back
Added
I have seen this C:\fakepath\xxxxxx.odb but how do I set it up
Well that was not too hard I hope I what I have done helps
Stuart

[jimk]

First, add a new sheet called “Mailman’s Route” that shows the sort order.

Enter the first route stop as “Kilmuir Lane” number 1. In the next row, use the formula =B2+1 for the number. Drag the street name and formula to fill down until number 10. Do likewise for the whole route, with formulas like either =B12-2 for decreasing or =B19+2 for increasing.

Then, on the sheet containing the data, add a column called “Sort Order”. This uses MATCH to look up the row from “Mailman’s Route” that contains that street and number.

=MATCH(C2&A2;'Mailman''s Route'.A$1:A$200&'Mailman''s Route'.B$1:B$200;0)

Finally, sort by the “Sort Order” column.

The result: Filtered and Sorted.ods

For more details about the MATCH formula, see @Lupp’s sample file that explains implicitly generated compound keys at How do you match multiple columns in Calc?.

[stuarts.burgers]

Thank you. Now I need to put some time aside to get my head around this, also I need the time to create the 400 Mailman’s routes I need so it will keep me off the streets for a while. At least it is winter here in NZ .
Thanks again
Stuart