Google-sheets – Displaying specific column header for cells, based on the cell value and its row header

google sheetsgoogle-apps

I have a very large table looking somewhat along the lines of the below example:

        Date1   Date2   Date3   Date4   Date5
        ShiftA  ShiftB  ShiftA  ShiftB  ShiftA
Person1 x       x       x       x       late
Person2 late    x       late    late    x
Person3 late    late    x       x       x

I'd like to find some way of displaying a list of the dates on which an individual was "late" to a certain shift type.

Using two input values, for example "Person2" and "ShiftA", it would yield an output "Date1", "Date3" (optimally the output would be in the form of a row with one cell for each date, but that's not necessarily within the scope of this question).

I've tried using things like nested filter() functions, query() functions and index() functions containing match(), but I keep running into different issues and I feel like I'm not quite savvy enough at Google Sheets to find a great solution to my problem.

Any push in the right direction would also be greatly appreciated!

Best Answer

The shape of your data makes harder to get the result that you are looking for. The first step in the "right direction", is to transform the data into a simple table structure like the following:

|   Person    |   Date   |   Shift  |   Value    |
+-------------+----------+----------+------------+
|   Person 1  |   Date1  |  ShiftA  |     X      |
|   Person 1  |   Date1  |  ShiftB  |   late     |

Once your data is transformed into the above shape it will be more easy to get

a list of the dates on which an individual was "late" to a certain shift type

You could use FILTER, QUERY, a Pivot Table, etc.

Related questions in this site

Related question in Stack Overflow

Other related in Stack Overflow

Related Topic