Dropdown.. Not in List ?

bcwilson.ca

Is there any way to find out the 63 "Vendors" in my sheet that is not in the dropdown for Vendors?

The only way I can figure it out is to create a Pivot for a count of the column and then compare to my dropdown list. 🤔

Andrée Starå

Hi @bcwilson.ca

Maybe you could copy the dropdown values and add two so-called helper columns and use FIND to see if the value is included or not.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @bcwilson.ca

Maybe you could copy the dropdown values and add two so-called helper columns and use FIND to see if the value is included or not.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

bcwilson.ca

That's what I did...

I pasted the dropdowns in a separate file then created a COUNTIF and counted the occurrences of the Vendor in the Vendor file using the Vendor in the sheet as a match..

=IF(COUNTIF({Vendor Master VendorName}, [Assigned Vendor]@row) > 0, 0, 1).. Now I get a RED Flag if the vendor is not in the Vendor Master File

So if it is Zero It is not in my drop down and it gets a red flag

Andrée Starå

@bcwilson.ca

Excellent!

Thanks for sharing!

bcwilson.ca

@Andrée Starå

I went a bit further and made the vendor file permanent..

Then I created a report based on things like the vendor status and the locations they service...

so filtering out the ones we do not do business with anymore and those that cannot service our out of city locations.

Then I run the report and copy the names to the clipboard and paste them in the dropdown..

Manual intensive but it works and if you set up a procedure then they are all maintained fairly easily