I’ve got a problem with my spreadsheet after using a filter function. Here’s what I’m dealing with:
I used this formula to filter my data:
=filter('Form Responses 1'!A:BJ,'Form Responses 1'!F:F=subjects!A19)
Now my table has a bunch of empty cells in the middle. What I want to do is move the data from columns N, O, and P into H, I, and J. Same goes for the other columns to the right of G - they should all shift left to fill in the gaps.
I’m not sure how to make this happen automatically. Any ideas on how to consolidate the data and get rid of those blank columns? Thanks for any help you can give!
I’ve dealt with similar issues before, and here’s a solution that might work for you:
Instead of moving data around after filtering, try using the QUERY function along with your FILTER. This approach can help you rearrange and consolidate your data in one step.
Here’s an example of how you might structure your formula:
=QUERY(FILTER(‘Form Responses 1’!A:BJ,‘Form Responses 1’!F:F=subjects!A19), “SELECT Col1, Col2, Col7, Col8, Col9, Col14, Col15, Col16”, 0)
Replace Col1, Col2, etc., with the actual column letters you want to keep. This will filter your data and then immediately reorganize it, eliminating those empty columns in the middle.
It might take some tweaking to get it just right, but once set up, it should automatically consolidate your data each time the filter criteria changes. Let me know if you need any clarification on this approach.
I’ve tackled a similar issue before, and here’s what worked for me:
Instead of trying to move the data around after filtering, I found it easier to restructure my original data first. I created a helper column that concatenated all the relevant data fields, separated by a delimiter. Then I used SPLIT() in combination with FILTER() to reorganize the data.
For example, if your data is in A:BJ, you could add a helper column with something like:
=JOIN(“|”,$A2,$B2,$C2,…)
Then use FILTER() on this new column, and SPLIT() to separate it back into individual columns. This approach automatically eliminates empty cells and keeps your data consolidated.
It takes a bit of setup, but once it’s in place, it’s much easier to manage filtered data without gaps. Hope this helps!
hey, i’ve been in your shoes before. here’s a quick fix: try using QUERY with your FILTER. it’ll help you reorganize everything in one go. something like this:
=QUERY(FILTER(‘Form Responses 1’!A:BJ,‘Form Responses 1’!F:F=subjects!A19), “SELECT Col1,Col2,Col7,Col8,Col9,Col14,Col15,Col16”, 0)
just swap out the column numbers to match what you need. it should do the trick!