Feb 23 2021 03:05 AM
Hello,
I am trying to use the new filter function on a table which contains 20K Lines. It looks this function is restricted to 5000 Line item. When I provide the range with in 5K rows , I am getting the results and if its more than 5K , the result is "#N/A" . Any solution available? Thanks
Feb 23 2021 04:45 AM
It seems only remotely possible that the FILTER function is limited to 5000 rows. Possible, but extremely unlikely.
Usually when we humans get a #N/A error message, it's we who have erred, not Excel. Usually. OR there's something in the data to which we're applying the function in question.
I'd check your syntax, all of the references, and then the data itself. There could be something in row 15,678 that causes the #N/A.
Do something like doubling (via copy and paste) the 5000 rows that DO work and see if your FILTER handles the resulting 10,000 rows.
Feb 23 2021 06:01 AM - edited Feb 23 2021 06:03 AM
You may perform small experiment.
New workbook, 1 in A1, in A2 enter =A1+1, Ctrl+Shift+Down, Ctrl+D. Now you have 1 048 576 rows in data.
Go to B1 and enter =FILTER(A:A,A:A>100000). It returns 948 576 rows of data.
Feb 23 2021 09:37 PM
@mathetes Thanks, that's right there is #N/A in cell after 5K. Ideally i want to filter the cells having #N/A . But if the array contains #N/A then filter function is throwing error #N/A.
Feb 23 2021 09:40 PM
Feb 23 2021 10:18 PM
@Sergei Baklan @mathetes .. Thanks for your help and i used ISERROR function to get it fixed.
Feb 26 2021 12:06 PM