Ask Your Question
0

Ordering Results from a Filter in Calac or in Base

asked 2018-06-04 00:14:02 +0200

stuarts.burgers gravatar image

updated 2018-06-05 09:34:29 +0200

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

C:\fakepath\After Advance Filtering.ods

C:\fakepath\DESIRED RESULT.ods

C:\fakepath\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.

edit retag flag offensive close merge delete

Comments

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

Jim K gravatar imageJim K ( 2018-06-04 20:48:25 +0200 )edit

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

stuarts.burgers gravatar imagestuarts.burgers ( 2018-06-05 09:05:08 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2018-06-05 17:41:03 +0200

Jim K gravatar image

updated 2018-06-05 18:00:37 +0200

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 https://ask.libreoffice.org/en/questi....

edit flag offensive delete link more

Comments

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

stuarts.burgers gravatar imagestuarts.burgers ( 2018-06-05 20:09:49 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-06-04 00:14:02 +0200

Seen: 49 times

Last updated: Jun 05 '18