Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter list by 1st few chars (2003 sp1)

    I have a spreadsheet of raw data that contains approximately 3500-5000 rows. I was able to create several pivot tables based different fields that work fine. Now, on a weekly basis, I need to take it one step further. I need another pivot table based on extracting rows where the location field begins with certain characters. If I only had 1 or 2 strings (at the beginning of a field) I see how I could do an advanced filter; but I have a list of approximately 30 different possible beginning characters and haven't been able to figure out how to do this. Example - need all rows where location begins with BJS* or CEB* or HKG*, etc. I appreciate the help in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter list by 1st few chars (2003 sp1)

    Say that location is column A.
    You could add a column to the table with formulas

    =LEFT(A2,3)

    etc. filled down as far as needed. You can use this column in AutoFilter or advanced filter.

    If I am completely off (which is quite possible), please provide more info about what you want to accomplish.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter list by 1st few chars (2003 sp1)

    I probably wasn't too clear. I've attached a copy of the file deleting all but 1000 rows (usually runs to about 3500-5000) to keep it under the 100 k max. Column J has the data I need to filter. I am probably missing something regarding adding a column and then pulling the first few letters of each row. I know how to do that, but then how do I ask for all rows that begin with certain characters, when there are about 30 different city codes I need to filter out from the rest? It isn't usually a column in the workbook; but I added the criteria list to column N so you can see what I'm looking for. I need to filter for all rows where the first 3 characters in the Location column J match any of the 30 Location Criteria items in column N. I get a new raw data file daily, so need to be able to do this every day. Thanks for all your help.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filter list by 1st few chars (2003 sp1)

    Add a new column with the formula suggested by Hans.

    Recreate the Autofilter including this column. Then you can do filter on this column and get what you want.

    Steve

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Filter list by 1st few chars (2003 sp1)

    Does the attached do what you want? It uses the advanced filter to show all codes that match any of the items in your list.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter list by 1st few chars (2003 sp1)

    I apologize that I haven't explained very well what I need. I'm attaching a new zip file that I added a criteria worksheet and a results worksheet to that will hopefully illustrate what I need. I don't just need a list of the codes. I need to extract the entire row for every code found in the location field that begins with the characters in the criteria list . I then need to put them on their own worksheet so I can manipulate the data via a pivot table separate from the other items in the raw data worksheet. I hope this is a bit clearer. I appreciate the assistance and sorry for the confusion.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filter list by 1st few chars (2003 sp1)

    You can create the extra column as suggested

    There is code at <post:=556,123>post 556,123</post:> which loops thru all the items and prints them, that could be adapted to copy and paste to a new sheet.

    If you are going the privot table route, instead of creating multiple sheets or even multiple pivot tables, I would create a pivot table with a page field.

    Add the extra column so you have a column with the frist three letters, then create the pivot table as desired using that column as the page field

    Then you can set the page field and have the pivot as requested.

    Steve

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Filter list by 1st few chars (2003 sp1)

    That's basically what my version did, except you would need to select the filtered list and copy the visible cells to a new sheet. You can do this by selecting the list, choosing Edit-Go to... Special.. and then choosing Visible cells only. Then just copy and paste. It should also be easy enough to automate that part with some code if necessary.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter list by 1st few chars (2003 sp1)

    Well I finally came out of the fog I was in and got this to work using Advanced filter with a wildcard for the location. Thanks to all.

  10. #10
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Filter list by 1st few chars (2003 sp1)

    I do this sort of thing quite frequently. In my case I just alter the data in the given column, but you could insert a column and get similar results.
    I custom filter on the column (in my case division) for Begins with ABC. I then change the top entry to ABC then a space. Next, I click on the little square on the lower right corner. This copies ABC down the entire list. I then change the filter to begins with XYZ and repeat the process.
    When i do my pivot tables I can then select that field (division) and will have separate rows for ABC and XYZ.

Posting Permissions

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