Forum Discussion
Filter table column by data validation error
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!
- My formula was something I found online.
Yes I am working with Excel 365.
I had not heard of lamda function in Excel. I will have to look into it. I have created macros before, but they are for specific tasks. I often wish I could just create a function and then use it in cells when I need it. I wonder if lambda functions will help with that.
