Filter out errors
- Feb. 02, 2014
 | Example files with this article: |
Introduction
Say you have a big table, which could contain errors. Several tens of thousands of records down the rows, times many columns… it is not an easy task to correct errors when working with big big data tables. In this article, I will explain to you a nifty Advanced Filter trick first. After that we will apply the trick to filter out records with errors in a table. This article is related to my previous article so you might want to read them both.
Advanced Filter basics
An Advanced Filter is a convenient way to set up filters on a table, that clearly go beyond the Autofilter capabilities. For example, to filter out all records for Country "Belgium" OR Amount less than 500, use an Advanced Filter like this:
FOLLOWED BY:
You must set up the filter criteria like I explain in the screen capture: the title names should be exactly equal to the names in the table header. If not, a match cannot be found. Also, you should use 2 lines in the criteria to have the effect of an OR condition. An AND condition would mean that you should use the same line in the criteria. Please experiment with this kind of filters, they are very handy, and will come back when you use database functions like DSUM, DCOUNT, DAVERAGE and similar functions. The result of the example filter should be:
An Advanced Filter can also optionally return only the unique records, as well as copy the results of the filter action to a different location.
An Advanced Filter trick
Let us get more fancy filter criteria! You will believe me that simple formulas are easy to incorporate:
The real trick can be seen in the next example (look at what formula is shown):
[Sidenote: as always, the separator for formula arguments is a semicolon on my system, it might be a comma for you.]
In the example, I filter out records for Country = "FRANCE" AND Dates that lie in last month. That "last month" is dynamic since the function TODAY() is used to get today's date. The function EOMONTH with arguments today and -1 will yield the last day of last month. In fact, any date in the past month is fine, since the TEXT function will convert that physical date into its month and year.
Here comes the exciting part: by referencing cell D2 in the table, we can implicitly apply a formula like this to all dates! Here we use row 2 since that row is the first row in the table containing data. The result of the formula happens to be TRUE since the condition of "being in last month" is TRUE for the first record in the table (though the Country does not equal France, but that is irrelevant here). You can use formulas for columns as long as you give the criteria column a name that does not exist in the data table header row. I used "Dates of the previous month", if I used "Date" the trick would not be possible.
This is only one example, but I am sure that you can find lots of use cases yourself, combining several conditions in an OR statement for instance. No doubt an Advanced Filter can handle much more advanced situations that the Autofilter cannot.
The filtered result is only 1 record:
Filtering records with errors
My main reason for writing this article, was to show you how we can apply bespoke Advanced Filter trick to a table to filter out all records that have at least 1 error, no matter what error type. Yes, it is possible and not tedious at all. Before showing you the method, here is the result based on the table used above:
The formula to check whether a record has one or errors, is:
=ISERROR(SUM(A2:F2))(Thank you Excel MVP Brad Yundt to point out this better alternative than my SUMPRODUCT formula)
Again, row 2 is used since it is the first row below the header. I do not use cell D2, but rather A2:F2: the entire first data record. The SUM function traps any errors in the 6 cells. The ISERROR function then does what its name suggests, and returns TRUE or FALSE (a YES or NO kind of result). If the result is TRUE, that record should be flagged! Excel will apply this formula to every record in the table and then filter out records.
As said above, name your criteria column unlike the names in the data table header: "Has_error" does not occur, so that is a good choice.
Voilá, you can now focus on the returned records with errors, rather than searching through (hundreds of) thousands of rows, or use an Autofilter. Indeed, an alternate solution would be to add a column to the data table, with our ISERROR() formula above, and copy that formula downwards, then use the Autofilter.
-
This is not an optimal solution, for at least 2 reasons:
- Excel must store and calculate all those hundreds of thousands of formulas - it might slow down your workbook
- You may not want to add columns to the data table and leave the table structure unaltered
If you want to only search for records containing 1 or more #REF! errors, you could in theory use the ERR.TYPE function and condition on error type = 4 for this kind of error. Note that cells without errors will have an error type equal to #N/A - so I skip these first with the IFNA function.
While this works within 1 cell, it appears that array formulas are not allowed in the filter criteria. At least, I could not get it to work tonight. But, the ISERROR function I used above does work in the filter criteria. What you could do, is use a COUNTIF formula:
=COUNTIF(A2:F2;NA())>0
=COUNTIF(A2:F2;"=#REF!")>0
…
The disadvantage is that in the second formula, we might need to rely on the localized function name. It is typed hardcoded within the quotes and will not change.
Even better, a User Defined Function in VBA is allowed too! Therefore:
Function Contains_REF_error(rng As Range) As Boolean''''' ' UDF to locate #REF! errors in a range or cells ' 02/02/14 '''''Dim r As Range For Each r In rng.Cells If IsError(r.Value) Then If r.Value = CVErr(xlErrRef) Then Contains_REF_error = True Exit Function End If End If NextEnd Function
Conclusion
In summary, I showed you a great trick for Advanced Filters. And then I applied it to filter out records with errors in a table.
