+ Reply to Thread
Results 1 to 10 of 10

Filtering out multiple "begins with..." (for letters) -- help!

  1. #1
    Registered User
    Join Date
    09-15-2016
    Location
    Cambridge, MA
    MS-Off Ver
    Excel 2011
    Posts
    7

    Filtering out multiple "begins with..." (for letters) -- help!

    Hi All,

    I'm working with a big data set, and need to filter out all the words that begin with certain letters from one column. The auto-filter only allows me to filter out a max of 2 "begins with...", and I specifically need to filter out 3 (e.g., all words beginning with the letters "A", "J", and "D"). I've looked into helper columns, advanced filtering... I can't seem to make it work.

    Thanks for any input!

  2. #2
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,675

    Re: Filtering out multiple "begins with..." (for letters) -- help!

    Here is a VBA solution that may work for you. Assumes data is in column A beginning in the second row. Hides all rows that have cells in Column A beginning with A,D or J
    Please Login or Register  to view this content.
    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    To run the Excel VBA code:
    1. Press Alt-F8 to open the macro list
    2. Select a macro in the list
    3. Click the Run button
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    09-15-2016
    Location
    Cambridge, MA
    MS-Off Ver
    Excel 2011
    Posts
    7

    Re: Filtering out multiple "begins with..." (for letters) -- help!

    Thank you for this!
    Can't tell if it's working, as Excel seems to crash every time I try to run it.
    Very eager to see if it works.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Filtering out multiple "begins with..." (for letters) -- help!

    Maybe add a new column with this formula:

    =OR(LEFT(A2)={"A","J","D"})

    Copy down as needed.

    Then you could filter on this column =TRUE.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    09-15-2016
    Location
    Cambridge, MA
    MS-Off Ver
    Excel 2011
    Posts
    7

    Re: Filtering out multiple "begins with..." (for letters) -- help!

    Thanks Tony Valko for your suggestion!
    This worked great for one cell (it gave the correct TRUE/FALSE result), but then I had trouble duplicating this for all the other rows (there are thousands). I kept getting an error message that said I was not allowed to change part of an array.

    Thanks again.

  6. #6
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,675

    Re: Filtering out multiple "begins with..." (for letters) -- help!

    Perhaps a sample portion of your spreadsheet so that we can test both solutions and determine why they are not working for you. Upload by clicking on the Go Advanced button at the bottom of the Reply window. Follow the instructions for uploading a worksheet.

  7. #7
    Registered User
    Join Date
    09-15-2016
    Location
    Cambridge, MA
    MS-Off Ver
    Excel 2011
    Posts
    7

    Re: Filtering out multiple "begins with..." (for letters) -- help!

    Oh I got it!

    Thank you so much again!
    So helpful.

  8. #8
    Registered User
    Join Date
    09-15-2016
    Location
    Cambridge, MA
    MS-Off Ver
    Excel 2011
    Posts
    7

    Re: Filtering out multiple "begins with..." (for letters) -- help!

    Thank you alansidman!
    I am certain I will run up against more issues, so will definitely post a sample with my question next time.
    Thank you again.

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Filtering out multiple "begins with..." (for letters) -- help!

    Quote Originally Posted by anderschender View Post
    I kept getting an error message that said I was not allowed to change part of an array.
    Can't understand why you'd get that message.

  10. #10
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,675

    Re: Filtering out multiple "begins with..." (for letters) -- help!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks. It is customary to give feedback to those that have helped you by clicking on the asterisk (Add Reputation) at the bottom of their post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Filtering item numbers with "Begins With"
    By badrum in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-24-2014, 03:41 PM
  2. Replies: 0
    Last Post: 03-21-2014, 09:58 AM
  3. [SOLVED] advanced filter criteria "begins with" and "does not begin with"
    By Eddie O in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 06:05 AM
  4. advanced filter criteria "begins with" and "does not begin with"
    By raph_baril in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  5. [SOLVED] Backup to specific folder if workbook names begins with "NSR" or "MAC"
    By GregR in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-05-2005, 08:06 PM

Tags for this Thread

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