Aug 7, 2022
FILTER has mismatched range sizes (ranges consistently specified)
The intention is that each row is checked in turn for all conditions of the filter() command (against multiple columns), all need to pass for the row to be copied/filters. Each row of data is self contained.
This worked perfectly when all I had were tests for non-empty cells: not(isblank())
Adding date check makes the filter die.
This is the first row of data:
Coria, Niceifolia, Seeds: Collect, 1 Jan, 6 aug, Hard (comment expected), [See Document]
If "today()" is "22 FEB" of any year then it passes the following test and therefore the row is wanted/copied:
("22 FEB" >= "1 JAN") AND ("22 FEB" <= "6 AUG)
I've used the fixed year of "1999" in place of any year.
={FILTER('Propagation Timing (Calendar)'!A2:F,not(IsBlank('Propagation Timing (Calendar)'!C2:C)))}
={ FILTER('Propagation Timing (Calendar)'!A2:F,
DateValue(Concatenate("1999-", month(now()), "-", day(now()))) >= DateValue(Concatenate('Propagation Timing (Calendar)'!C2:C, " 1999"))
)
}
={
FILTER('Propagation Timing (Calendar)'!A2:F,
not(IsBlank('Propagation Timing (Calendar)'!C2:C)),
DateValue(Concatenate("1999-", month(now()), "-", day(now()))) >= DateValue(Concatenate('Propagation Timing (Calendar)'!C2:C, " 1999"))
)
}
It reports:
Error
FILTER has mismatched range sizes. Expected row count: 999. column count: 1. Actual row count: 1, column count: 1.
Now I haven't found any decent description of ranges so I could be doing something wrong but I'm specifying them consistently between the working and non-working filters. The major difference is I'm using ">=" for a greater than or equal comparison.
Is there anyway to see WHERE in the filter it fails? Is there a debugger?
Details
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Last edited Aug 8, 2022
Aug 8, 2022
Hello Again Dennis!
I made a new tab on your sample sheet called MK.Help and attempted to surmise what you're doing.
I put this formula in cell A2:
=FILTER('Propagation Timing (Calendar)'!A2:F, 1*('Propagation Timing (Calendar)'!C2:C&" 1999")<=1*TEXT(TODAY(),"m/d/1999"), 1*('Propagation Timing (Calendar)'!D2:D&" 1999")>=1*TEXT(TODAY(),"m/d/1999"))Is that doing what you're hoping?
Cheers,
Matt
Matt
Original Poster Dennis Bareis marked this as an answer
Aug 7, 2022
Apart from formula syntax issues (eg. wrapping a filter in {curly_brackets} when it isn't required), it appears that you are asking the formula to filter when a specific date is >= ALL other dates in the range:
DateValue(Concatenate("1999-", month(now()), "-", day(now()))) >= DateValue(Concatenate('Propagation Timing (Calendar)'!C2:C, " 1999"))
When what is required is to filter the range when it is less/greater than or equal to the specific date.
So this is why you have the error saying there is just 1 row instead of the 999 rows.
To get the best help, please share a sample spreadsheet that shows realistic-looking sample data, and manually enter the desired results you would like to see, into the same cells where you would like see them. Also, clearly indicate where the sample data is, and where the desired results are (eg. by highlighting them with colour), and give clear written explanation of how you derived/calculated your results.
If you want to, you can provide a copy of your own file, and ensure that you have enabled Editor access for Anyone with the link.
Alternatively, you can use this form to get a sample spreadsheet file, into which you can build your sample/example:
Last edited Aug 7, 2022
Original Poster Dennis Bareis marked this as an answer
All Replies (12)
Aug 7, 2022
={
FILTER('Propagation Timing (Calendar)'!A2:F,
true
)
}
Apart from formula syntax issues (eg. wrapping a filter in {curly_brackets} when it isn't required), it appears that you are asking the formula to filter when a specific date is >= ALL other dates in the range:
DateValue(Concatenate("1999-", month(now()), "-", day(now()))) >= DateValue(Concatenate('Propagation Timing (Calendar)'!C2:C, " 1999"))
When what is required is to filter the range when it is less/greater than or equal to the specific date.
So this is why you have the error saying there is just 1 row instead of the 999 rows.
To get the best help, please share a sample spreadsheet that shows realistic-looking sample data, and manually enter the desired results you would like to see, into the same cells where you would like see them. Also, clearly indicate where the sample data is, and where the desired results are (eg. by highlighting them with colour), and give clear written explanation of how you derived/calculated your results.
If you want to, you can provide a copy of your own file, and ensure that you have enabled Editor access for Anyone with the link.
Alternatively, you can use this form to get a sample spreadsheet file, into which you can build your sample/example:
Last edited Aug 7, 2022
Original Poster Dennis Bareis marked this as an answer
Aug 7, 2022
If {} is invalid and that is not reported by sheets then I'd have to assume this is a sheets bug. It doesn't cause any failure and I saw these using in sheet examples.
For example "now()" is "7 AUG", if the row's data is "1 JAN" to "6 AUG" then I want to see that row in the copied data. My question is how to do that? the "isblank()" works using exactly the same column specification (the format of which I haven't seen documented) that fails for my date comparisons...
I haven't found any filter documentation (at least this isn't it: https://support.google.com/docs/answer/3093197), can you point to some that explains in details what it does and what the conditions mean including what "A2:A" means, if I use "!A2" or "!A2:A2" then it doesn't work.
Last edited Aug 7, 2022
Aug 7, 2022
Because FILTER returns an array, the use of {} in this case is redundant (of no use), and so using it without reason is confusing your issue.
As explained in my previous reply, you are asking the formula to filter 1 value where it is bigger than or equal to all the rows, like this:
if value >= A1:A10000000
This part of the formula must be switched around to be like this
if A1:A10000000 < value
Because we don't have any data to see what you are really trying to do, anything further is purely speculative and we could be here for a very long time discussing the how's and why's without getting you the results you want.
The link you provided shows a perfect example of the syntax:
FILTER(A2:B26, A2:A26 > 5, D2:D26 < 10)
Whereas you are trying to do this:
FILTER(A2:B26, 5 < A2:A26, 10 > D2:D26)
I hope this makes more clear the situation.
Last edited Aug 7, 2022
Aug 7, 2022
Hello Dennis,
Paste some data from your propagation timing (calendar) sheet here on this sample sheet created specifically to answer this question and we'll see if we can figure out what's going on!
Cheers,
Matt
Matt
Aug 7, 2022
Note that I had tried changing the order of the condition bits, using GTE(), LTE(), <=, >= condition variations.
If sheets or columns can be "named" like ranges then I'd like some pointer to documentation on this please... It would seem a major oversite for at least sheets not being able to be named (an alias to distinguish code from visual references, i.e. call my data sheet via the alias "DataSheet")...
Also, can constants be defined so "C2" doesn't need to appear in a billion places? I'd have thought what I was trying to do (what to do at a particular time of year) wasn't that unusual but I couldn't find any good templates, is there a non Google place I should be looking at?
I have coded in many languages for many years but in case it is not obvious, I have not done this for Excel or any other type of spreadsheet.
I have coded in many languages for many years but in case it is not obvious, I have not done this for Excel or any other type of spreadsheet.
Last edited Aug 8, 2022
Aug 8, 2022
In the interest of getting you the results you need, i would like to present a website that poses an interesting concept, known as the "XY Problem":
The reason for posting that site is to bring your attention that rather than going down a long road discussing many different types of functions without knowing what is really needed as an end result, we can save each other a lot of valuable time if you can provide manually calculated results and just explain how you worked out the results, and let us focus on developing formulas that will do exactly what you need to achieve the results automatically.
Aug 8, 2022
If there are alternative solutions then I am also open to those but basically one way or another I wish to see filtered views of the data (base) held in a "sheet". If my attempted solution doesn't work then I am hoping to understand how as I am just starting with Google (or any other) spreadsheet programs. I have yet to find any decent documentation and what I have found from google is as minimalistic as their apps.
Each row of the data is (intended to be) filtered independently (as there seems to be some confusion about this).
Last edited Aug 8, 2022
Aug 8, 2022
Hi Dennis,
Thanks for sharing a sample!
you wrote "Each row of the data is (intended to be) filtered independently"
But your formula looks like you're trying to filter each column of the data "independently". Just want to double check on this part of your goal?
But your formula looks like you're trying to filter each column of the data "independently". Just want to double check on this part of your goal?
Hello Again Dennis!
I made a new tab on your sample sheet called MK.Help and attempted to surmise what you're doing.
I put this formula in cell A2:
=FILTER('Propagation Timing (Calendar)'!A2:F, 1*('Propagation Timing (Calendar)'!C2:C&" 1999")<=1*TEXT(TODAY(),"m/d/1999"), 1*('Propagation Timing (Calendar)'!D2:D&" 1999")>=1*TEXT(TODAY(),"m/d/1999"))Is that doing what you're hoping?
Cheers,
Matt
Matt
Original Poster Dennis Bareis marked this as an answer
Aug 8, 2022
Part of the confusion is that we don't know what you mean by "filtered independently"
Giving formulas that do not work doesn't explain what it means, this is where giving manually calculated results can illustrate what you are trying to explain. Think of a photo being worth 1000 words, this also applies to giving example of what you want a formula to do for you. :)
kind regards.
Aug 8, 2022
This does appear to do what I want:
=FILTER('Propagation Timing (Calendar)'!A2:F, 1*('Propagation Timing (Calendar)'!C2:C&" 1999")<=1*TEXT(TODAY(),"m/d/1999"), 1*('Propagation Timing (Calendar)'!D2:D&" 1999")>=1*TEXT(TODAY(),"m/d/1999"))At least after I fixed it from being valid only in the US:
TEXT(TODAY(),"m/d/1999") => TEXT(TODAY(),"1999-m-d")
The only real difference I can see is that you use:
- "&" 1999" in place on "Concatenate()"
- Removed datatype()
Performance reasons?- "1*(whatever)"
What does the "1*()" do?Why don't I need it on checking for blank columns (or do I?)?Where is it documented?
I tried to use a logical or for data part missing or in range and that fails ("1*" issue I assume):
OR( not(IsBlank(1*('Propagation Timing (Calendar)'!C2:C))), 1*('Propagation Timing (Calendar)'!C2:C&" 1999") <= 1*TEXT(TODAY(),"m/d/1999") ),1*('Propagation Timing (Calendar)'!D2:D&" 1999") >= 1*TEXT(TODAY(),"m/d/1999")
Similar for (OK if one of C or D cells is blank):
OR( not(IsBlank('Propagation Timing (Calendar)'!B2:B)), not(IsBlank('Propagation Timing (Calendar)'!C2:C)) )
Just like "not(isblank())" how would I also check that columns have passed data validation (no red flag):
To answer recent questions by @Matt:King and @Mr Shane, I have add this to the OP:
The intention is that each row is checked in turn for all conditions of the filter() command (against multiple columns), all need to pass for the row to be copied/filters. Each row of data is self contained.This worked perfectly when all I had were tests for non-empty cells: not(isblank())Adding date check makes the filter die.This is the first row of data:Coria, Niceifolia, Seeds: Collect, 1 Jan, 6 aug, Hard (comment expected), [See Document]If "today()" is "22 FEB" of any year then it passes the following test and therefore the row is wanted/copied:("22 FEB" >= "1 JAN") AND ("22 FEB" <= "6 AUG)I've used the fixed year of "1999" in place of any year.
Last edited Aug 9, 2022