How to shift data in filtered Google Sheets table ignoring empty cells?

Other Questions Google Sheets
1

Hey everyone! I’m working on a Google Sheets project and I’m stuck. I’ve got a table that I filtered using this formula:

=filter('Form Responses 1'!A:BJ,'Form Responses 1'!F:F=subjects!A19)

The problem is, it’s leaving a bunch of empty cells. What I need to do is move the data from columns N, O, and P to H, I, and J. Same goes for the other columns to the right of G - they should shift left to fill in the gaps.

I’ve been scratching my head over this for hours. Any ideas on how to make this work? I’d really appreciate some help figuring out a way to ignore those blank cells and shift everything over. Thanks in advance!

2

hey there ClimbingLion! i’ve run into similar issues before. have you tried using the QUERY function instead? it can be more flexible for this kinda thing. something like:

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

then you could use array formulas to rearrange the columns how u want. hope this helps!

3

I’ve tackled this issue before, and I found a solution that might work for you. Instead of using FILTER, try combining QUERY with ARRAYFORMULA. Here’s what I’d suggest:

=ARRAYFORMULA(QUERY(‘Form Responses 1’!A:BJ, “SELECT " & JOIN(”,“, IF(LEN(‘Form Responses 1’!A1:BJ1), COLUMN(‘Form Responses 1’!A1:BJ1), )) & " WHERE F = '” & subjects!A19 & “'”))

This formula dynamically selects only non-empty columns and then applies your filter condition. It should give you a clean result without those pesky empty cells.

If you still need to rearrange columns after this, you can wrap the whole thing in another ARRAYFORMULA and use INDEX to pick the columns in the order you want. It’s a bit complex, but it gets the job done efficiently.

4

I’ve encountered this issue before in my data analysis work. One effective approach is to use a combination of QUERY and ARRAYFORMULA functions. Here’s a formula that might solve your problem:

=ARRAYFORMULA(QUERY(FILTER(‘Form Responses 1’!A:BJ, ‘Form Responses 1’!F:F=subjects!A19), “SELECT " & TEXTJOIN(”,“, TRUE, IF(LEN(QUERY(FILTER(‘Form Responses 1’!A:BJ, ‘Form Responses 1’!F:F=subjects!A19), “SELECT *”, 1)), “Col” & COLUMN(INDIRECT(“A1:BJ1”)), )) & " LABEL " & TEXTJOIN(”,", TRUE, IF(LEN(QUERY(FILTER(‘Form Responses 1’!A:BJ, ‘Form Responses 1’!F:F=subjects!A19), “SELECT *”, 1)), “Col” & COLUMN(INDIRECT(“A1:BJ1”)) & “‘’”, )))

This formula combines filtering, removes empty columns, and shifts the data as you described. It’s complex but should achieve what you’re looking for. Let me know if you need any clarification on how it works.