Extract rows from a big table based on criteria in the Same column

Welcome to the forum!

Will you please attach a sample Excel workbook? Include the array formula that you already have that works with one criterion per column.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Hi Madzilla and welcome to the forum,

I'm pretty lazy and it seems to me you are looking for an Auto Filter. If you click anywhere in the data and then on the Data Tab and then the Filter Icon, you will get filter dropdowns on your Code, Country and Price headings column. Now simply click the dropdown in each column and filter away. See the attached showing what I mean.

Also, if you make your data a "Table" you can insert a Slicer Tool and filter using this newer tool.

http://blog.contextures.com/archives...in-excel-2010/

AutoFilter for Madzilla.xlsx

Hey Madzilla,

I've see a few of the "Formula Guru's" on this site use an Array Formula that does a filter and then packing of the desired data like you want. I think there are about 4 people that can produce those long CSE formulas. I'm an Advanced Filter guy and would write a macro behind the Advanced Filter to press a single button and do all the work.

When you suggest you have a lot of different files that you want to apply this filter to, this leads me to a newer tool in Excel called Power Query. Using it, you can create a filter and apply it to any file you point to. You can also apply the filter to an entire folder worth of files and append all of them together.

Your problem reminds me of some data cleanup I did a few years ago. If there were less than 50 then I could do it manually about as fast as I could write code to do it. If over 100, I'd surely create a formula or macro to accomplish the task. In your case if you daily get a file that you need to filter like you suggest above, AND you don't know how many rows the data has, I'd be learning Power Query.

My concern is that using a CSE formula, you would still need to define the number of rows (and columns?) the data has and modify the formula. If you didn't do this but instead used a Dynamic Named Range of data, you still need to apply the DNR. A lot of this answer depends on how you get the data and how you need to see the data in the end.

https://www.microsoft.com/en-us/down...3-17638048727f