To explain, we get products from many vendors, each has their own way to define categories. I need a lookup table to convert them all into a single table standardising the categories. Some vendors have just a small change in spelling i.e S&W vs Smith and Wesson or Smith & Wesson. So if Smith & Wesson is the name defined as the standard, then S&W and Smith and Wesson will be converted to the standard name based on a second table.
I could write a case calc instead of a lookup table (since I don't think that will work), but that would be massive and near impossible to keep current as they will add new Categories and I would not know about it. Import would add a new records after updating the dataset, but the case wouldn't have a statement to accommodate the new Category.
My thought was to run the import data through a lookup table to copy over the standardised name, then I could compare the new list to the lookup table and add any new categories or flag them so I can periodically check for new and define the standard name.
Issue is that I can't think of a way to dynamically define the different name spellings so that the lookup table has the criteria needed to match it to a standard name. Which brings me back to a Case calc.
That updated and standardised category list will be pushed to update an e-commerce website, before the updated and standardised product list is updated. This way I don't have to reset. (Sorry, obscure and not really related but I wanted to kind of explain the end goal to some degree.)
Thanks for letting me pick your brains.