How to compact filtered data in Google Sheets by removing empty cells?

Other Questions Google Sheets
1

I’ve got a problem with my Google Sheets table after using a filter function. The data is spread out with blank cells in between. Here’s what I need to do:

  1. I’ve used this filter: =filter('Form Responses 1'!A:BJ,'Form Responses 1'!F:F=subjects!A19)
  2. Now I want to ignore the empty cells and move the data to the right
  3. For example, columns N, O, P should shift to H, I, J
  4. All columns after G should move left, filling in the gaps

Is there a way to compact the data automatically? I’m not sure how to approach this. Any formulas or scripts that could help would be great. Thanks for any advice!

2

I’ve encountered this issue before, and there’s a neat workaround using array formulas. Try this:

=ARRAYFORMULA(FILTER(TRANSPOSE(QUERY(TRANSPOSE(‘Form Responses 1’!A:BJ),999^99)), ‘Form Responses 1’!F:F=subjects!A19))

This formula transposes your data twice, which effectively pushes all non-empty cells to the left. The FILTER function then applies your original condition. It’s a bit of a hack, but it works well for compacting filtered data without empty cells.

Just be aware that this method might alter the original column order, so you may need to adjust the output columns if specific ordering is crucial. Also, it could be slower on very large datasets.

3

hey Ethan, u could try using QUERY function instead. it’ll automatically remove empty rows. something like:

=QUERY(‘Form Responses 1’!A:BJ, “SELECT * WHERE F = '” & subjects!A19 & “'”)

this should give u the compact data ur after. hope it helps!

4

I’ve dealt with similar issues before, and here’s what worked for me:

Instead of using FILTER, try the QUERY function combined with ARRAYFORMULA. This approach not only removes empty rows but also allows you to restructure your data on the fly. Here’s a formula that might do the trick:

=ARRAYFORMULA(QUERY(‘Form Responses 1’!A:BJ, “SELECT A,B,C,D,E,F,N,O,P,Q,R WHERE F = '”&subjects!A19&“’ AND (N IS NOT NULL OR O IS NOT NULL OR P IS NOT NULL)”))

This formula selects specific columns (adjust as needed) and moves them next to each other, ignoring empty cells. It’s a bit complex at first, but once you get the hang of it, it’s quite powerful. You might need to tweak it based on your exact data structure, but it should give you a good starting point.

Remember to double-check your results, especially if you’re working with sensitive data. Good luck!