I need help with splitting my main data into separate sheets based on categories. Let me explain what I’m working with.
I have a master sheet with student information and their preferred subjects:
A | Math
B | Science
C | Math
D | History
What I want to do is create individual sheets for each subject. So the “Math” sheet would only show:
A | Math
C | Math
I’m looking for a way to automatically filter and display only matching entries on each category sheet. The tricky part is I don’t want blank rows where non-matching entries would be. Is there a formula or method in Google Sheets that can pull only the relevant data and compact it without gaps?
for sure! try using the FILTER function, like =FILTER(A:B, B:B=“Math”). this will give you just the math entries without any gaps, and it updates automagically when you add more data. super handy!
Apps Script is another solid option if you want more control. I dealt with something similar - inventory data that needed splitting by categories across multiple sheets. FILTER and QUERY work fine for basic stuff, but I found scripting gave me way more flexibility with larger datasets. Plus it creates the category sheets automatically. The script loops through your unique categories, makes new sheets if needed, and fills them with filtered data. Really handy when you’ve got tons of categories or they keep changing. Beats manually setting up formulas on every sheet.
QUERY function is perfect here and way more flexible than basic filters. Try =QUERY(A:B,"SELECT * WHERE B='Math'") on your Math sheet. It handles dynamic data great - automatically updates when you add students to the master sheet. I’ve used this tons for sorting project data by department and it completely fixes the blank row problem. The syntax looks scary at first but it’s super powerful once you get it. You can even sort results in the same formula if you want.