Filtering out empty cells in Google Sheets using JOIN and QUERY functions

Other Questions Google Sheets
1

Hey everyone! I’m just starting out with Google Sheets and I’m trying to figure out how to filter data based on two matching values. I’ve got this formula that almost does what I need:

=JOIN(", ",QUERY('Form responses 1'!$D$2:$E,"SELECT D WHERE E = '"&$A$2:$A&"'"))

It works pretty well, but there’s one annoying problem. When there are a lot of empty cells, I end up with a bunch of extra commas in my results. Does anyone know how to tweak this formula so it skips over the blank cells? I’d really appreciate any help or tips you can give me. Thanks in advance!

2

I encountered a similar issue when working with JOIN and QUERY functions. A solution that worked for me was incorporating the ARRAYFORMULA and IF functions. Try this modified formula:

=JOIN(“, “, ARRAYFORMULA(IF(QUERY(‘Form responses 1’!$D$2:$E, “SELECT D WHERE E = '”&$A$2:$A&”’ AND D <> ‘’”) <> “”, QUERY(‘Form responses 1’!$D$2:$E, “SELECT D WHERE E = '”&$A$2:$A&“’ AND D <> ‘’”), )))

This approach filters out empty cells before joining, ensuring you don’t end up with those extra commas. It might look a bit complex at first, but it’s quite effective. Let me know if you need any clarification on how it works.

3

hey sky24, i’ve been there! try adding FILTER to remove empty cells:

=JOIN(“, “,QUERY(FILTER(‘Form responses 1’!$D$2:$E, ‘Form responses 1’!$D$2:$D<>””),“SELECT Col1 WHERE Col2 = '”&$A$2:$A&“'”))

this should do the trick. lmk if u need more help!

4

I’ve been working with Google Sheets for a while now, and I think I’ve got a solution that might help you out. Instead of using JOIN and QUERY, you could try using TEXTJOIN. It’s a bit simpler and handles empty cells automatically. Here’s a formula that should work:

=TEXTJOIN(", ", TRUE, FILTER(‘Form responses 1’!$D$2:$D, ‘Form responses 1’!$E$2:$E = $A$2))

The TRUE parameter tells TEXTJOIN to ignore empty cells, so you won’t get those extra commas. The FILTER function selects only the cells in column D where the corresponding cell in column E matches your criteria in A2.

This approach is not only cleaner but also more efficient, especially if you’re dealing with large datasets. Give it a try and see if it solves your problem.