CODEWITHSUNDEEP
arraysif-statementgoogle-sheetsindexinggoogle-query-language
Susan Bourenane
Susan Bourenane

Reputation: 1

Retrieve both column header and row header for EACH value in sheet

I have a sheet that lists the duty roster for teachers. The row headers contain the date of the teacher's duty, and the column headers contain the time segments (9:00 to 9:30, 9:30 to 10:00, etc).

The teacher's name is in the cell intersecting the date and time segment. A teacher's name will appear in multiple rows, at potentially different time segments.

I would like to write a script that will search for a specific teacher and return a list showing all the dates, and the time segment for that date to which the teacher has assigned duty.

I appreciate any and all help. Thank you.

Sample data

Upvotes: 0

Views: 62

Answers (1)

player0
player0

Reputation: 1

its done like this:

=ARRAYFORMULA(SORT(IFERROR(SPLIT(FLATTEN(IF(B2:D4="",,
 A2:A4&"×"&B1:D1&"×"&B2:D4)), "×"))))

enter image description here

extend ranges as you need and then to filter it down you can use dropdown and QUERY() it

https://developers.google.com/chart/interactive/docs/querylanguage

Upvotes: 1

Related Questions