I’m managing tutoring sessions for my college course and need help with Google Sheets organization. Right now I collect availability through a form that returns data organized by student names, with their available time slots listed for each day.
I want to flip this around so the data is organized by time slots instead. Each time slot would get its own row, and all the student names who are available during that time would be listed together in one column.
I’ve already split the time data so each time appears in separate cells, but I’m stuck on how to reorganize everything by time slots rather than by names. Each tutoring session lasts 30 minutes and students can only book one session per week due to limited spots.
I also need to group consecutive time slots together so instructors don’t have gaps in their schedule. For example, instead of having separate 2:00-2:30 PM and 2:30-3:00 PM slots on Monday, they would teach from 2:00-3:00 PM continuously.
Rearranging the data this way would make it much simpler to create the weekly tutoring schedule since I could see which time slots have the most demand and group them efficiently.
I actually tackled something very similar when organizing lab session signups for my research group. Instead of trying to transpose everything at once, I found it easier to create a new sheet with predetermined time slots as your foundation. Set up columns for day, time slot, and available students. Then use a combination of QUERY and CONCAT functions to pull student names based on matching time criteria from your original data. The QUERY function is particularly powerful here because you can filter by multiple conditions simultaneously. For the consecutive slot grouping, I created a separate analysis column that checks if the same students appear in adjacent time periods. When there’s sufficient overlap between consecutive slots, you can merge them programmatically. The beauty of this approach is that it preserves your original form data while creating a completely restructured view for scheduling purposes. Just make sure to reference absolute cell ranges in your formulas so they don’t break when you add new data. This method saved me hours each week compared to manually reorganizing everything.
This sounds like a classic transpose problem that I’ve dealt with before when scheduling employee shifts. You’ll want to use a combination of UNIQUE and FILTER functions to pull this off efficiently. First, create a master list of all possible time slots using UNIQUE on your time data. Then for each time slot, use FILTER to find all students available during that specific time. You can concatenate the results with TEXTJOIN to get all available students in a single cell per time slot. For the consecutive time grouping challenge, I’d recommend creating a helper column that identifies which students are available for back-to-back slots. You can then use conditional logic to determine when it makes sense to combine slots based on overlapping availability. The key is setting up your formulas to automatically update when new form responses come in, otherwise you’ll be manually adjusting the schedule every time someone submits availability. I learned this the hard way when managing volunteer schedules.
honestly pivot tables might be ur friend here - way easier than messing with complex formulas. just select your data range, insert pivot table, drag time slots to rows and student names to values section. google sheets will automatically group everything by time periods and u can see who’s availble when at a glance. much simpler than the formula approach imo