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):
HW188441021582232,1188451021580188461021580188471021580188481021580188491021580188501021580
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!
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): HW188441021582232,1188451021580188461021580188471021580188481021580188491021580188501021580 This results in only row 18844 and 18848 being pulled by the Filter function, giving 2232,1 and 0I 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! Read More