Forum Discussion
Filter table column by data validation error
What is the best way to filter table column data if data validation is applied with "warning" and the green error triangle appears for invalid entries?
A good way to start would be by being clear on what you're asking for. And I'm referring to your question here in this forum. Quite frankly, I think the reason you've had no replies despite over 30 views is that it's not even clear what kind of situation you're describing. It would help us help you if you would give us some more details, examples, that kind of thing... Even attach or post a copy of the spreadsheet--on OneDrive or Google Drive, just making sure there's no confidential info in it. And post a link here.
Wow! The question seemed clear to me. I have the same issue.
I have a table and I just added a data validation rule to one of the columns. The value in the column must be in a named lists.
Because I created the table without validation and added it later, I now have many cells in that column with invalid entries. They look like this, with the triangle.
When I select the cell then I see the warning icon.
It is a data validation error, but I cannot do a screen capture because the hover tooltip goes away when I hit <win><shift>s.
Because my table has over 12000 rows, I would like an easy way to just show the rows with the validation error so that I can easily fix them. (The content of the cell is not always the same so I cannot search for Unknown.)
Let's say the column with data validation is named My Data.
And the named range you used for the data validation rule is MyList.
Insert a new column in the table to the right of My Date.
Enter the following formula in the first data row of the new column.
=ISERROR(MATCH([@[My Data]], MyList, 0))
Excel should propagate the formula to all data rows.
The new column will contain TRUE for rows with data validation errors.
You can filter the column for TRUE.
HansVogelaarThanks!
I probably should start using auxiliary columns more. I can hide them so they do not clutter up the table.
I did come up with a formula to put in a conditional rule to highlight the cells with validation errors.
=COUNTIF(INDIRECT("Accounts!$A2:$A158"),"="&$B4) <> 1
I do not like it thought.
- It actually duplicates the validation which is bad.
- it has a cell range, which might not auto update. (I hate that conditional rules cannot use structured table references. Conditional formatting on tables is messy and error prone, but necessary for so many reasons.)
- It does not clearly convey its meaning.
Being a programmer by trade, all of the above go against my training.
I am going to try your method and try your formula in the conditional formatting.
It is too bad that ISERROR() does not have a way to check for data validation errors, or alternatively add a new excel function DATAVALIDATIONFAILED() to Excel.
Hi julie777
"It actually duplicates the validation which is bad"
There are worse indignities that working with spreadsheets inflicts!
I hope you are working with excel 365. It would be downright cruelty to expect a programmer to use anything less! Your working formula looks a little bit overcomplicated to me, I normally try to avoid INDIRECT. You suggested a function to test the validation results and it could be done with VBA. Meanwhile, why not write your own Lambda function?
DATAVALIDATIONFAILED(range, validList, [marker]) = LET( result, 1 - COUNTIFS(validList, range), IF(ISOMITTED(marker), result, IF(result, marker, "")) )With this as a Lambda function you could
1. annotate the Table externally (using a spilt array of course)
2. insert a helper column to show the validation result (relative reference replicated down the table)
3. collect the offending records together in a summary table
4. return an overall statement of success or failure
= DATAVALIDATIONFAILED(Table1[ID], validationList, "→") = IF(DATAVALIDATIONFAILED([@ID], validationList),"û", "") = FILTER(Table1, DATAVALIDATIONFAILED(Table1[ID], validationList), "Validation OK" ) = IF( OR(DATAVALIDATIONFAILED(Table1[ID], validationList)), "Validation fail", "Validation OK" )with a bit of conditional formatting thrown in if that is to your taste!
