+ Reply to Thread
Results 1 to 8 of 8

Excel 2003 help - search through tables

  1. #1
    Registered User
    Join Date
    07-30-2008
    Location
    UK
    Posts
    52

    Excel 2003 help - search through tables

    hi,

    i have a simple table with various criteria and 4000 odd entries which I want to filter properly. For example, 1 column has a long list of postcodes, and next to it, each postcode corresponds to an order date, in dd/mm/yyyy format - all on 1 worksheet.

    I want to be able to link this data to another worksheet, where I can then pick out a particular area of postcode by searching for 2 letters, i.e. Oxford = 'OX'. And then it would show me the list of postcodes beginning with 'OX', along with its corresponding order date. From that list, I then want it to show out of those dates, only entries where the date is later than a certain date - for example, All postcodes beginning with OX with order dates later than 30/06/2008, for example.

    So far all I've done is put a custom filter on each column, but there must be a more clever way of doing this, and without having to manually add filters.

    Would really appreciate some help with this.

  2. #2
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    PivotTables

    You are right, there is more clever way - super clever ;-)
    It's called PivotTables.

    You could start by have a look at an example + some short tutorials in this link.
    http://excelforum.com/showpost.php?p...01&postcount=9
    Then - if you need help - post a sample and someone can probably help you.

    HTH
    Ola

  3. #3
    Registered User
    Join Date
    07-30-2008
    Location
    UK
    Posts
    52

    Smile

    thanks for those excellent links, it seems that pivottables are indeed the way forward - I'd still like someone to give me a quick step by step though, regarding my specific need in the 1st post - Here is a sample:

    Postcode Order date
    DE11 3LU 30/03/2007
    OX53 DE1 30/05/2008
    NE24 57W 12/10/2007
    DE48 3LU 09/04/2008
    LE57 1GD 06/07/2007
    BL52 9FD 05/01/2008
    OX57 6HJ 05/08/2008
    OX59 6YS 09/09/2008
    DE27 K28 05/08/2008

    So this is a sample from the raw data in the worksheet. What I want to be able to do, to summarize, is to be able to show in another worksheet, linked to the raw data, a table showing ONLY postcodes beginning with 'OX', AND only from order dates After 30/06/2008. I just don't know how to put 2 matching criteria onto 1 thing.

    Please help - my job depends on it!

  4. #4
    Registered User
    Join Date
    07-30-2008
    Location
    UK
    Posts
    52
    can anyone help with this at all?

    i really need it!

  5. #5
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Autofilter or PivotTable

    After seeing the example. Autofilter is probably better.
    (to find tutorial of Autofilter, follow the above mentioned link)

    I have also encl. a file showing both an Autofilter and a PivotTable.

    Hope this helped
    Ola
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    07-30-2008
    Location
    UK
    Posts
    52
    absolutely amazing - thanks so much.

  7. #7
    Registered User
    Join Date
    07-30-2008
    Location
    UK
    Posts
    52
    quick question - how did you create the pivot table like that - all i need to know is with the 4 headings, 'postcode, order date, begins with OX?, After date' - where do you place them in the 'construct your pivottable' bit. I can't get my head round how the page, row, data and column sections work together.

    thanks in advance. my excel knowledge has tripled today!

  8. #8
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    ...

    Thanks for the appreciation.

    Right click on the PivotTable and select Wizard..., and then Layout...
    There it shows which field I have put where.

    Once the PivotTable is created you can try and double click on the headers. Drag and drop the different headers. Right click and so.
    Learning PivotTables are just like driving. After some time you just get a feeling for it, without really knowing how you got it.

    Happy to hear that the explaining works
    Ola

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1