How to find Items in Sheet that are not in dropdown list?

Cheree Peterson

We have a column that has a dropdown list. I have it selected where what is entered has to be in the dropdown list but have admins can override the entry. This is ok because I don't want them to be slowed down when adding a new entry. However, I would like to go back after the fact to find entries that are not in the drop down list to verify if they are a new entry or a duplicate that was overlooked and add them to the drop down list. I get a message telling me how many entries are NOT in the drop down list but I am having trouble finding a way to locate them either through a filter or a report. Any suggestions?

Paul Newcome

You would need to make a list of all of the available options. From there you can set up a hidden flag column with a formula to reference the list and flag any rows containing data that is not within the reference list. Then you can filter to show only rows that are flagged.

Paul Newcome

You would need to make a list of all of the available options. From there you can set up a hidden flag column with a formula to reference the list and flag any rows containing data that is not within the reference list. Then you can filter to show only rows that are flagged.

Cheree Peterson

Thank you. When I create a report and filter on that column. What order are the drop down list options to filter by sorted in (they are not alphabetical) and is there a way to change this?

Do the entries that are not in the drop down list appear as an option to filter by here?

Paul Newcome

The default sorting of a report is by the rows in the sheet. In the report builder toolbar there should be a sort option that will allow you to adjust this.

I'm not sure I follow for your second question though.

Cheree Peterson

So I am not talking about the sorting of the report itself but instead when I go into a Report and select filter, when I want to filter a report by a column that has a drop down list - the drop down list is sorted in a weird way (not alpha exactly) seems to be sorted first by how many times it is used?

2nd question I answered myself I believe: When I go into a report and select filter, then try to filter on a column that uses a drop down list - am I able to filter by an item that is a rogue entry (ie not on the specified drop down list) or does it only show the items manually entered on drop down list. I did a test on this and it does show all items whether on the specified drop down list or rogue entries but there is no way to know which ones are on the drop down list and which ones aren't. I wish it would somehow show the items that are not in the drop down list.

I was hoping there was a way through a report to accomplish your solution of exporting the list, creating a new sheet with that list, adding a flag column to show which ones are not on the list and then creating a report to summarize all the flags. Because now the original drop down list in the first sheet and the new list with the export both have to be manually updated/reconciled.

Paul Newcome

I'm not sure how that particular list is created for the filtering options.

Yes. It is a bit of a pain to have to manually keep two lists up to date, but that is the only way to do it without the premium add-on Data Shuttle. If you have access to that, you can set up an export and then a separate import that will grab the list and automatically update the dropdown options.