May 05 2021 12:40 PM
May 05 2021 12:40 PM
I'm building a template and in this template I have multiple fields for different parameters that will need to be filtered. One filter is for time and I'm having trouble figuring out how to filter multiple time periods i.e. filter out data from times between 7-9AM and 1-2PM. I'm able to filter for one period but have not been able to figure how to do more than one without using VBA, because I don't know how to use VBA. Any help would be appreciated and if more information is needed to help solve this, please let me know.
May 27 2021 08:23 PM - edited May 27 2021 08:32 PM
What I created for your filter problem is not a formula based solution, but rather a power query solution.
Step 1. I converted your Raw Data Adjusted into a table and did a power query to it:
then I created a parameter table to connect your Filters&Results sheet where you enter your search parameters and called it ParamConnector.
then I did a power query on the Parameter table
and made three references to it and named them:
Search1Results, Search2Results, and Search3Results
On Search1Results I did the following:
1 I added a custom column then entered M language to select the rows as prescribed in the 1st Shade Omissions Period start and end dates:
the M Language:
(InnerTable) => InnerTable[Date] >= [SD] and InnerTable[Date] <= [ED] and InnerTable[Time] >= [R1S] and InnerTable[Time] <= [R1E]
R1S= Start Time Range number 1 in Filter&Results Sheet
R1E= End Time Range number 1 in Filter&Results Sheet
Repeat and Rinse for the other 2 Search2Results and Search3Results making sure the variable references are referening the correct parameters. (Check it in the Added Custom step in the Query Settings navigation panel by clicking the gear setting beside it)
After all three search results have been done, I then did a Merge query >Append on all three parameter queries Search1Result, Search2Results, and Search3Results.
then I Closed & Loaded to the worksheet and named it 3VectorSearchResults sheet:
Here is the file => My Template Neer Cap Test
When you enter new parameters in your Filter&Results Sheet, press ctrl+alt+F5 to refresh the query.
if you have any questions please feel free to reach out.
May 28 2021 02:12 AM
I think there were errors in the way in which elements of the filter criterion were formulated. Additional parentheses were needed.
=FILTER(siteTime#:finalColumn, (Date#<=endDate)*(Date#>=startDate) *((Time#>eT₁)+(Time#<sT₁)) *(POA#>=minIrr) *((Time#>eT₂)+(Time#<sT₂)))
I converted the raw data to an Excel table and started building the derived data as dynamic arrays with a defined name applied to the anchor cell for reference purposes.