Excel 2010 Filtering Rows Based on Greater than / Less than Match List

I found this thread Excel 2010 Filtering Rows Based on Match List and wanted to know if the same, or a similar function could be used to filter if I had two columns with start/stop times on my first sheet, and I wanted to show only rows on my second sheet that start within those times.  I was thinking maybe a greater than column A and less than column B to compare the dates?

First Sheet
Start-Stop.JPG
Second Sheet
Start-Stop2.JPG
KelairnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Attach an example workbook/ It will be more useful than images of data.
0
Glenn RayExcel VBA DeveloperCommented:
I'm restating to ensure I understand your request:

You have a workbook with two sheets.  On the first sheet, you have two columns of date/time data representing Start and Stop times.  On the second sheet, you have a larger data table which also contains Start and Stop times.  These values do not necessarily correspond with any start or stop times on the first sheet.

You want a method to filter the data on the second sheet based on values in the first sheet.

1) In your example you show 27 pairs of Start and Stop times.  Do you want to filter the second sheet by the minimum Start and maximum Stop time shown?  If not, how do you want to select the Start and Stop time criteria?

2) Will a macro-based solution be acceptable?  For example, a Worksheet_Activate event could be created to automatically filter the second sheet's data by criteria determined on the first sheet.

Regards,
-Glenn
0
KelairnAuthor Commented:
Here is an example workbook.

The start and stop times on the first sheet do not necessarily correspond with any start or stop times on the second sheet.

I want a method to filter the data on the second sheet based on values in the first sheet, and a macro is fine.

I need Sheet2, Start Date-Time to be greater than or equal to Sheet1, Start Time and less than or equal to Sheet1, Stop Time

Pretend that Sheet 1 start time is when I turned on the lights in my shop, and stop time is when I turned them off.  Sheet 2 is a motor status.  I want all my motor status for when the lights are on.
Start-Stop.xlsx
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Rob HensonFinance AnalystCommented:
You can use Advanced Filter for this.

On your Sheet1 in column D put Header Start Date-Time (Same as Data list) and formula below against each of the rows in columns A & B:

=">"&TEXT(A2,"dd/mm/yy hh:mm")  (cells D2 to D28 in your example)

Repeat for column E, header End Date-Time and similar formula below:

="<"&TEXT(B2,"dd/mm/yy hh:mm")

On sheet3 copy the headers form Sheet2 into A1 to D1.

Select a cell away from the headers in Sheet3 and start the Advanced Filter wizard, Data tab > Sort & Filter Group > Advanced.

Select the Copy to another location option
Use the selection browser icon to
Set the List Range to the Data range on sheet2
Set the Criteria range to the new formula based columns on Sheet1 (D1:E28 in example)
Set Copy to range to headers on Sheet3 (A1 to D1)

Click OK.

If this is going to be a repeated task then this can be set to a VBA routine with variable inputs as required.

Thanks
Rob H
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Slight tweak, for the formulas on sheet1, it might be better to use "=>" & "<=" so that you capture occurences that include the start and stop times rather than just occurences between them.

Thanks
Rob H
0
Rob HensonFinance AnalystCommented:
For some reason, doesn't give results when using "=>" & "<=" so leave with just ">" & "<".

See attached file with results.

Thanks
Rob H
Start-Stop.xlsx
0
KelairnAuthor Commented:
Thanks so much!  This was exactly what I needed.  My day and month stamps were reversed, so I had to change the order in my filter list, but once I did that, it worked like a charm.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.