Formula to simulate the effects of multiple filters?

[HumcoAnalyst]

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.

 

[jasonb75]

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

=IFERROR(INDEX(facility_column,SMALL(IF(facility_column=facility_criteria,IF(admission_date_column<=reference_date,IF((discharge_date_column>reference_date)+(discharge_date_column=""),ROW(facility_column)))),ROWS($A$1:$A1))),"")

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

 

[HumcoAnalyst]

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.

 

[jasonb75]

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.

 

[jasonb75]

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.