# Formula to simulate the effects of multiple filters?

#### HumcoAnalyst

##### New Member
I have been asked to build a report based on data extracted from an access database. The query itself was built (Not by me, I do not have those skills) and getting an individual month worth of data is easily accomplished by filtering multiple columns by the criteria I'm looking for. The problem comes in as I have to do this for every month dating back to 1/1/2010, and for 25 different facilities.

What I need is a formula that can simulate the filter function described above
The order of operations is:
Filter the list by facility
Filter the list by admission date. Date<=target month (for reference, I'm using the 1st day of each month as a point in time count)
Filter the list by discharge date. Date>target month, OR Date=""

I've tried various Countif/s, AND, OR formulas, but have not been able to make the process work. I'm sure there is a simple way to do this, but I cant find it. Any help would be appreicated, and I can post examples if my description isn't clear enough.

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Welcome to the board!

Is there any reason for not using the normal filtering tools?

Your post suggests that you have a significant amount of data to work through, in which case the formula required for the task could be way beyond what most systems are capable of processing.

edit:- as an example, you would need an array formula on the lines of (descriptions used in place of ranges).

And that's just for the first column of data.

Last edited:
Filtering isn't really an option due to the volumn of data that I have to go through, plus the likelyhood of a fat finger error is too high. What i've come to the conclusion with the help of some co-workers, is that a macro to perform the steps might be the way to go. Thank you for the example though, that was helpful, and a different approach to the formula than I was trying.

Filtering isn't really an option due to the volumn of data that I have to go through

Assuming that filtering is taking to long to process due to the data volume, then yes, vba (a macro) would be the best option.

Using formula to filter the data will be significantly slower than using the filtering tools.

Without knowing how you need to compile your reports, one of my thoughts would be to separate the data into 25 workbooks, 1 for each facility.

That way, when you perform subsequent filtering for the 2 date columns, effectively you will only have to sift through an average of 4% of the total data volume for each facility instead of 100%

edit:-

I hadn't noticed that you were working with data pulled from access, assuming that the queries are live, this suggestion is probably useless.

Last edited:

Replies
6
Views
3K
Replies
0
Views
616
Replies
10
Views
4K
Replies
15
Views
638
Replies
1
Views
857

1,203,456
Messages
6,055,543
Members
444,794
Latest member
HSAL

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back