Thread: 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:

Number   Color   Animal   Shape   State
   1     Green     Dog    Square    WI 
   2     Blue     Bird              WA
   3     Blue      Cat    Circle    CA

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:

Enter a number:   2              Enter a color:  
Resulting row:     
Number   Color   Animal   Shape   State
   2     Blue     Bird              WA

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:

Enter a number:                   Enter a color:  Blue
Resulting rows:
Number   Color   Animal   Shape   State
   2     Blue     Bird              WA
   3     Blue      Cat    Circle    CA

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.

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.

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.

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.

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...

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

@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.

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