0

Please see original posted question which was made under the assumption that Google Sheets did not have certain limitations that Excel does not.

The accepted answer provides two solutions which appear to work correctly in Excel when I tried them, but when using the second of the two solutions in sheets I get the following error message: FILTER has mismatched range sizes. Expected row count:20, column count:1. Actual row count:1, column count:1.

Second solution: =LET( _A,B27:B46, _B,D3:D12, _C,FILTER(_A,(ISNA(MATCH(_A,_B,0)))*(_A<>""),""), _D,FILTER(_A,(ISNUMBER(MATCH(_A,_B,0)))*(_A<>""),""), IF(INDEX(_C,1,1)<>"",VSTACK(_C,_D),_D))

After doing some digging I believe I understand the issue and it seems Sheets just doesn't process overflows like Excel does and it wants me to tell it what range the output should populate to.

My issues are that I am not sure which FILTER equation it has a problem with, though I assume it is both.

And more crucially, this particular syntax is a tad above my understanding. I've tried a few things to modify it based on what I've seen for fixes on more basic equations, but I don't seem to be getting it right.

Improve this question
0

1 Answer 1

Reset to default
0

The reason the formula won't work in Google Sheets is that it's using an incorrect syntax.

Microsoft modeled their implementation of filter() after Google, but they chose to make it a tad incompatible. To make the formula work, you can remove the ,"" and use ifna() instead, like this:

=let( new, tocol(B27:B46, 1), old, D3:D12, first, filter(new, isna(match(new, old, 0))), second, filter(new, match(new, old, 0)), vstack(ifna(first, tocol(, 1)), ifna(second)) )

Note that there are simpler ways to do the same in Google Sheets. One alternative is to use sort(), like this:

=sort(tocol(B27:B46, 1), match(tocol(B27:B46, 1), D3:D12, 0), false)

To catch the case where the "new" list is empty, and to make the logic easier to follow, add let() and iferror():

=let( new, tocol(B27:B46, 1), old, D3:D12, iferror(sort(new, match(new, old, 0), false)) )

Google Sheets has had array-enabled functions such as filter() and sort() that "overflow" or "spill" their results from the get-go since 2006.

See sort(), filter(), let(), tocol(), iferror() and match().

Improve this answer
6
  • I had a feeling it was the ,"". I've tried all 3 of these and the first one does one thing I like over the other two. Where multiple names are repeated from week 1 to week 2 it will maintain the order that those names are entered, where the other two consider previous entry. The first one does put a blank over repeats when it's all old names, but if any new names are on the list it does not consider blanks. Hoping this will be popular enough that it's not an issue, but I plan to modify the signup retroactively based on physical attendance anyway. Thank you! Commented Sep 1 at 12:11
  • Playing around with the tocol equation using 1 and 3 at the end produces 1 blank, but 2 will include all blanks in the interest column. Commented Sep 1 at 12:20
  • Yeah, the tool in sheets said as much on 1-3, using 0 just results in no repeated entries carried over. I see that 1 and 3 should ignore blanks, but those options bring at least a "unique" blank entry over. Commented Sep 1 at 13:53
  • I don't believe I asked an additional question. I was just trying to provide feedback on the solution you provided. Commented Sep 1 at 16:11
  • Fair enough. Edited the answer to skip a zero-match new list. Commented Sep 1 at 18:38

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.