Closed Thread
Results 1 to 10 of 10

Excel 2007 : Filtering large amounts of data

  1. #1
    Registered User
    Join Date
    11-24-2010
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Question Filtering large amounts of data

    Hi!
    I'm wondering if someone can point me in the right direction for a filtering project I need to do. I'm dealing with several large spreadsheets of data (about 12 columns and several thousands of rows) holding information about customers. I need to be able to apply a filter to the company name column against a list of 189 company names that cannot be sold to. If one of the 189 company names appears in the list of customers, the whole row (all of that customer's info) should be deleted. I have tried doing this already by using the regular filters and deselecting the 189 company names that mustn't appear, but since this is fairly time consuming, I'm wondering if there is a way to set up a filter that refers to another sheet where the 189 companies are listed. This would avoid having to deselect all of them each time, and would reduce the chances that one is accidentally missed. The filter should be able to be applied to multiple spreadsheets as more customers are generated.
    Thanks!

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Filtering large amounts of data

    1) On Sheet2, list all the bad customers in column A. Spelling is critical, no hidden spaces!

    2) On your data sheet, add a "Key" column to your data to do this Customer Name test, you will be filtering by this key column later

    3) In the key column , do a TRUE/FALSE test for the customer name against your table of bad customer names.

    =ISNUMBER(MATCH($A2, Sheet2!A:A, 0))

    4) Now AutoFilter that key column for all TRUE values

    5) Delete all visible rows all at once

    6) Turn off the Autofilter and remove the key column
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    11-24-2010
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Filtering large amounts of data

    I'm with you all the way up to step 4 - how do you AutoFilter for TRUE values? I've selected the Filter button for the Key column, but all the numbers in it are 0 - for bad and good customers.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Filtering large amounts of data

    Then the formula is not finding matches. There is something different about the value in A2 and the cell it should match in the bad customer list. Look for hidden spaces, try TRIM(A2) instead of just A2 in that formula.

    The formula should not be giving 0 answers anyway, it's a TRUE/FALSE formula.

    If you continue to have problems, click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

  5. #5
    Registered User
    Join Date
    11-24-2010
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Filtering large amounts of data

    Great, I got it to work! Is there any way to cahnge the formula so that it looks for contents rather than an exact match? For example, if it were XYZ Inc, but only XYZ is listed on the bad customer list?

    Thanks so much!

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Filtering large amounts of data

    Post an example set of data so we can look at the same things.

    Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

  7. #7
    Registered User
    Join Date
    11-24-2010
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Filtering large amounts of data

    OK, so in my example, I've put the 'Bad Customers' on the second worksheet. When you apply the filter, you'll see there are only 2 that are 'Good Customers'.

    Right now, BCE Inc is FALSE because they're not on the Bad Customers list. But say I wanted to add BCE Inc to the named list of Bad Customers. I would need to make sure that any data that comes in with BCE Inc (or any variation that contains those letters, like BCE, Inc., BCE Incorporated etc) would also be labeled as TRUE or Bad. Is that possible?
    Attached Files Attached Files

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Filtering large amounts of data

    To use "substring" searches, you'll need an array formula to test the sub strings listed in your bad customer list to the full string in your database. See attached.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    05-22-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Filtering large amounts of data

    This is superb. However, it would appear that the test of the substrings only returns TRUE for the first row in the Bad Customers sheet. IF EFG is added to the Bad Customers tab, the key value does not change to True. If EFG is added as the first value to replace ABC then it does. Any ideas. I'm a little stumped.
    Thanks, Paul

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Filtering large amounts of data

    Ulthima,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

Closed Thread

Thread Information

Users Browsing this Thread

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

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