Forum Discussion
jblanchard1055
Jul 19, 2024Copper Contributor
Excel Filter() Function Skipping Data
Hello all!
I am completing an analysis based on a very large master data set (around 50.,000 rows). I have encountered an issue in one specific calculation I am trying to program. Basically, a part of my algorithm for this calculation is using the Filter() function to find specific values based on an ID and two other criteria. However, I noticed that many expected values for a test cell with the formula were missing (e.g., I expected a dozen rows to return for this test cell but only 5 or so results would appear. I managed to find a very specific test example with a small subset of the data I have that gives the same error:
Formula is: =FILTER(DB!W$18844:W$18850;DB!H$18844:H$18850=A2;0), A2 is 102158
Data pulled directly from my report (apologies, I use the French Canadian version of Excel which uses commas and not periods for decimals):
H | W | |
18844 | 102158 | 2232,1 |
18845 | 102158 | 0 |
18846 | 102158 | 0 |
18847 | 102158 | 0 |
18848 | 102158 | 0 |
18849 | 102158 | 0 |
18850 | 102158 | 0 |
This results in only row 18844 and 18848 being pulled by the Filter function, giving 2232,1 and 0
I have checked the "Evaluate Formula" tool and it successfully matches the ID number (column H) to all 7 rows as it gives the array {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE} at the step where it compares A2 to the range provided and it does not provide any further calculation steps after this. I have no idea what to check next to find the source of the error. Any ideas?
Thanks!
- Never mind. I just found the issue. The sheet where I input my test formula had filters present, which meant that rows 3-5 and 7 were hidden and that is where the filtered data would populate.
- jblanchard1055Copper ContributorAdded note, I have just found the F9 function for in-formula testing. Even the formula : =FILTER({2232,1;0;0;0;0;0;0};{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE};0) still only gives me two results: 2232,1 and 0.
- jblanchard1055Copper ContributorNever mind. I just found the issue. The sheet where I input my test formula had filters present, which meant that rows 3-5 and 7 were hidden and that is where the filtered data would populate.