[Solved] Postcode Finder

[Jameswong]

Hi i have loooked through the boards but cannot find exactly what i am after so i really appreciate any help. Im very much a newbie when it comes to calc but i will try and explain my problem as clear as possible. Ive recently started doing some work as a courier driver and the company sends a list of all apoointments for the week on a spreadsheet to all their drivers on a monday something like this, in colum a would be full post codes and in b the times. al1 323 12/7/07
al1 472 11/7/07
cxj 393 09/07/07

Basically the spread sheet is for all drivers nationally and contains thousands of appointments which are taking me me hours to sort through finding the ones for my area. Is there a way that if i have my area in colum a of a sheet and then copy the list of appintments and dates to colum b and c that calc can find matches from the first part of the code in B with any codes in my area in A and either copys the whole of that cell in b to a cell in d to the right of the correspnding date or maybe deletes all cells that don't match with mine in a something like this
al1 al1 323 12/7/07 al1 323
al2 al1472 11/7/07 al1 472
al3 cxj 393 09/0707
Thank you for any help you can offer

[Villeroy]

Select the whole list.
Data>Filter>AutoFilter

[Jameswong]

Thanks alot for the reply but i couldn't get that to work maybe im doing something wrong? i used the filter but all i could do was one row at a time , what i really need is the whole of b which is around 20000 full post codes compared to a which is around 50 post codes and the matches that are found in b to be duplicated to d or to remain whilst the rest not found in b are deleted. The codes in a are of the form al1,bl2,de3 etc and the codes in b are in full form al1 372, al4 796, bd2 469 etc.

Thanks again for any help

[Jameswong]

Hi there first of all big thank you for the pointers but im afraid although very informative i find my self no nearer to understanding. The formula in question 4 you pointed to, where exactly do i put that and what do i put in the value to search for, would it be all the cells in A with all my codes. I tried many combinations but when i would use the auto filter i cant filter by the value 1, the only options are ALL,TOP 10, Standard filter and then every post code in that coloum. I realise i am properly not understanding something very simple so i will study the ten concepts again tommorow with hope of better understanding. Thanks again for the pointers.

[Villeroy]

http://wiki.services.openoffice.org/wik ... ced_Filter
The "advanced filter" reads filter criteria from cells.
Write up to 8 criteria into a separate list below a header row:

zip
1234
4567
4331
9876
9876

Select your original list, call Data>Filter>Advanced...
Point to the criteria list.

The above list will filter the column labeled "zip" by value 1234 OR 4567 OR 4331 OR 9876 OR 9876

[mkoenig]

Could you attach a portion of your spreadsheet? With that and a list of postcodes you are looking for I think VLOOKUP would work. I would be happy to see what I can do.

[Jameswong]

Thanks again to all, I would really appreciate if you could take a look at it for me as i really am quite confused. In column A are the post codes of the area i cover and in b and c there is a portion of the list i was sent from work this week. Sorry if im repeating myself but what i really want is a quick way that once i get sent the list, all appointments that do not match my area codes are either deleted or the codes that do match are maybe duplicated to d or something. Basically once i have found appointments on the list that match my area i am suppose to send that list of my appointments back to office to confirm i can do them. This week it took my over an hour sorting through the list finding codes which were in my area and by the time i had sent them back alot of the jobs had gone to another worker who was far quicker than me in replying. So again thank you all for trying to help i really do appreciate it.

[karolus]

Hallo

Done, with →Data→Filter→Specialfilter ( with Option [x]RegExpression )
see Attachment:

Appointments_Filter_special.ods

(21.33 KiB) Downloaded 141 times

Karo

[Jameswong]

Karo that is brilliant that is exactly what i am looking for thank you. With the risk of sounding even more clueless how exactly do i do that myself? Could you please give me an idiots guide step by step. What you have done is exactly what i would love to be able to do. Thank you very much i can see the end in sight now.

[karolus]

Hallo

Sorry, I am realize your OOo-version -
You have to update to OOo 3.4 ( ?? ) or LibreOffice 3.4 or higher , to deal with more as 8 Kriterias in →Data→Filter→Specialfilter

If you have updated, see my Exampledocument.
You have to extend your Areacodes with .* ( see Sheet 'Filterarguments' )
The Columnheader of Filterarguments should be the same as the Header of Areacodes in Sheet 'Input'

Klick into Inputrange goto →Data→Filter→Specialfilter,..
enter your Kriteriarange: Filterarguments.A1:A156
enter the Startcell for Output 'Filteroutput.B1'
check Option: [x]Regular Expression

Karo

[Jameswong]

Thank you so much that is genius, after updating and following your instruction i have got it to work nearly! I seem to have made a mistake somewhere as unlike your example sheet where all the times and dates are with their correct code on the output sheet my times and dates do not appear on the output just the correct code with #### in the adjacent cells in C. Can you please point me out to where i have gone wrong? Thank you for this help its brilliant.

[karolus]

Hallo

..correct code with #### in the adjacent cells in C...

The Column is to small to show full output, →Rightklick on Columnsheader→ Optimal Width...

Karo

[RoryOF]

Usually in Calc the #### is an indication that the cell is not wide enough. Select the column, right click and choose Optimal Column Width.

[Jameswong]

ah ha that is brilliant so simple it makes me feel silly but so happy at the same time. You can't imagine how grateful i am truly. Big thank you Karo for sticking with me and guiding me through i really do appreciate it and big thanks to all of you who have taken your own time out to help me through my problem. I really can't stress how much time this will save me so again, thank you all.

James