Excel, Filter a group of rows using the same criteria.

Question

So I've been tasked with working on a weekly resource planner. I want to be able to filter each week to show any absences or other time off. Each day would have A for absence, S for sick etc. My Current issue is trying to filter the overall week, If i try and filter it the filter will only use the options within the first column, so if no one was sick on Monday but then was on Tuesday they wouldn't appear after I remove blanks. is there a way around this?

For example, In this week while while 2 of the columns would show, the 3 that have no input on monday would be filtered out.

Answer 1

You can probably use Advanced Filter. See:

Excel Advanced Filter Introduction

Excel Advanced Filter – A Complete Guide with Examples

Answer 2

Hi,

Your data is not well structured. Using the Query Editor, you should use the "Unpivot Other Columns" feature. This will reshape the date to a row expanding one with all weeks appearing in a single column.