Forum Discussion

jblanchard1055's avatar
jblanchard1055
Copper Contributor
Jul 19, 2024
Solved

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): 

 HW
188441021582232,1
188451021580
188461021580
188471021580
188481021580
188491021580
188501021580

 

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! 

 

  • jblanchard1055's avatar
    jblanchard1055
    Jul 19, 2024
    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.
  • jblanchard1055's avatar
    jblanchard1055
    Copper Contributor
    Added 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.
    • jblanchard1055's avatar
      jblanchard1055
      Copper Contributor
      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.

Resources