+ Reply to Thread
Results 1 to 11 of 11

Filter large database using Advanced Filter

  1. #1
    Registered User
    Join Date
    01-28-2011
    Location
    Eagan, MN
    MS-Off Ver
    Excel 2007
    Posts
    6

    Post Filter large database using Advanced Filter

    I have a project I'm trying to do for work. I have a catalog of about 280 entries (this will grow) with 8 columns each. Each row is numbered sequentially in the left-most column, and my goal is to have a user be able to type in a number in a separate field in order to populate an entire row with the rest of the data from that row. For example:

    Please Login or Register  to view this content.

    If the user would enter a 2 in the field, the rest of that row would auto-fill a blank row with the rest of the data (Blue, Bird, , WA). On top of this, I would like to make it so the user could enter any value from any of the columns and that would also fill the rest of the row data in the blank row. Here's what I mean:

    Please Login or Register  to view this content.

    To make this even more complicated, some of the cells in the columns have the same value (like the color Blue), and I want to make it so that if the user were to enter the value Blue, all the rows with Blue in them would be auto-filled in the blank rows:

    Please Login or Register  to view this content.

    I have tried using functions to make this happen, but I am running into quite a few issues. I have played with macros to accomplish this, but I'm short on time and am hitting too many walls.

    Any help would be GREATLY appreciated, and I will try to better explain this project if I need to.
    Last edited by thegrimmster; 02-10-2011 at 10:58 AM. Reason: Title change

  2. #2
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: VBA Macro Help

    thegrimmster,

    Welcome to the Excel Forum.

    You could use AdvancedFilter with Criteria and Extract ranges.

    To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

    The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

    To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,860

    Re: VBA Macro Help

    Hi thegrimmster and welcome to the forum,

    I wrote some code for a person a few weeks ago that might work for you, with some modification. It uses Advanced Filters like stanley suggested above. Look at the attached on Sheet2. The data is to the far right and it is a long list. There is an Advanced Filter Criteria range starting in K1 to R2. I have a list of all possible words listed lower in Columns K-M. The user double clicks on words (or blanks to clear the filter) and the results shows up in Colums A->I.

    There is some fancy VBA behind behind this sheet. There is Event Code behind sheet3 to see if and where a double click happened. I then pluck what was double clicked and put it in the Advanced Criteral Range and do the Advanced Filter.

    See it this helps at all. If not, start looking at Advanced Filters and see the posibilities.

    I agree with Stanley also in that a sample file is your best bet on getting a good answer.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: VBA Macro Help

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  5. #5
    Registered User
    Join Date
    01-28-2011
    Location
    Eagan, MN
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: VBA Macro Help

    I apologize for the title, I looked over the rules before posting, and still broke rule #1.

  6. #6
    Registered User
    Join Date
    01-28-2011
    Location
    Eagan, MN
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Can't properly filter a large database using Advanced Filter

    So, I was playing around with MarvinP's macro (thank you so much for the reply by the way) to accomplish what I need. His code is almost exactly what I need to do, but I unfortunately can't get it to do what I want with the correct cells. I am still working on this, but at the same time, I am creating an autoCAD drawing for each of the 280 entries in my Excel file, so I am very short on time.

    If someone could please look at the attached .xlsx file and see if they can help, I would really appreciate it. Like I say in the file, I would ideally like to accomplish this without using a macro (my company's security is ultra tight and users won't understand why/how macros are being blocked). But, if using a macro is the only way, that would be fine.

    Thanks for any future replies.
    Attached Files Attached Files

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,860

    Re: Can't properly filter a large database using Advanced Filter

    Hi thegrimmster,

    You don't need macros to do Advanced Filters. You can do them by hand without a macro.
    Put your cursor anywhere in the Table (Colums K-R) and go to Data and click on Advanced Filter.

    The Dialog will appear with your data range already selected. You will need to tab down to the Criteria Range and select the Range from A2 to G3. Then you will need to click the Radio Button to "Copy to another location and then click in the last Copy to: box. You will need to select cell A10 for that range and click the OK button. You will need to also insure that the output range is cleared or overwritten with the new data each time.

    The Macro simply performs the above steps, but you can do them on your own without a macro. After doing it a few times, the default ranges may be set so you don't need to input them again.

    I hope this helps. I'm off to study Reapply and why they have it greyed out. Maybe there is a way...

  8. #8
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Can't properly filter a large database using Advanced Filter

    thegrimmster,

    Detach/open workbook thegrimmster - EF762154 - SDG15.xlsm.

    Make entries (one at a time) in range A3:G3, and the Worksheet_Change Event will pull the data from your database to your Results area.

    There is a command button Clear Entry that will clear range A3:G3, instead of manually deleting each cell separately.


    Have a great day,
    Stan
    Attached Files Attached Files

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,860

    Re: Can't properly filter a large database using Advanced Filter

    @Stan,

    I agree that yours is a great answer. I'd even put a dropdown to fill in the Criteria area. The user wanted to see if it would be possible without using any VBA. I was trying to show him/her how they needed to do it by hand. This obsticle of training users might then really allow the IS department to allow macros to exist in the workbook.

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Can't properly filter a large database using Advanced Filter

    Re: non VBA alternative - see if the attached is of interest

    (of course you could just apply standard Filters to the catalog directly)

    edit: attachment reloaded - typo in one formula
    Attached Files Attached Files
    Last edited by DonkeyOte; 02-05-2011 at 06:58 AM.

  11. #11
    Registered User
    Join Date
    01-28-2011
    Location
    Eagan, MN
    MS-Off Ver
    Excel 2007
    Posts
    6

    Thumbs up Re: Filter large database using Advanced Filter

    Many thanks to all that replied.

    I've been messing around with all the code provided, and have determined that complex macros are high over my head (which is ironic since I have C++ and Java classes under my belt...). But, thanks to the great people in this forum, I now have 2 solutions to my problem.

    DonkeyOte's elegant non-VBA alternative is exactly what I had pictured in my head originally but couldn't accomplish, and I think that is the option I will be going with at first. These formulas will be easier to change as I add data to my catalog, and people here at work who don't know what macros are will not see any warnings. Also, with the little time I have to throw this together, it is the quicker solution right now.

    Both Stanley's excellent VBA solution and MarvinP's great VBA example have taught me ALOT about VBA in Excel, and I will be using snippets of both of these to create a macro-enabled version of my catalog for future use. Having VBA enabled will allow me to have more functionality down the line.

    Thanks again to everyone who helped. You make my brain feel extremely tiny and inferior...

+ Reply to 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