2
3
I've been trying to solve this for a good few hours now, but with no result...
My setup:
I've got excel sheets of cosmetics product recipes.
Each recipe contains dozens of ingredients, each given by its trade name.
I've got a long list translating each trade name to its 'scientific' name.
I need to translate each trade name into its scientific counterpart. This part is very easy to do with a vlookup.
But some trade names are recipes in themselves, containing multiple ingredients in the table of scientific names. So it's not a 1:1 relationship, sometimes (not always) it's 1:many.
For example:
Cosmetic A sheet
IngredientA_trade_name
IngredientB_trade_name
IngredientC_trade_name
Trade to Scientific names list master file:
IngredientA_trade_name Science1
IngredientB_trade_name Science2
IngredientB_trade_name Science3
IngredientB_trade_name Science4
IngredientC_trade_name Science5
IngredientC_trade_name Science6
....etc, for lots and lots of ingredients.
Now, I've found ways to return multiple values, but none of these does the job automatically:
I must put the Ingredient name in a cell, then place the formula to the cell next to it and drag it down a few places so it autofills for all possible matches. Some other solutions put the returning values in a single cell (Science2,Science3,Science4) which is a bit better but doesn't work for the rest of my workflow...
Is there a way to go through the list of trade names and insert rows as necessary containing all the scientific names?
So the basic issue is that the lookup stops with the first match and you need it to find all matches? Roughly how many records are in your master file? Roughly how many scientific names are there? Can a given scientific name be associated with more than one trade name (be a component of more than one ingredient)? In the same cosmetic? Roughly how many cosmetics are there? What's the upper limit on the number of ingredients in a cosmetic? The upper limit on the number of master file records associated with one cosmetic? (These numbers are to rule out approaches that wouldn't be practical.) – fixer1234 – 2015-10-01T10:47:05.227
Hello jcbermu! Yes, the problem w/ vlookup is that it stops after it encounters the first instance.
-master file has ~6000 scientific names, with more added every now and then
-typical recipe can have up to 30 ingredients.
-Indeed a scientific name can exist in >1 ingredients of the same recipe.
-3 thousand active recipes (in stock)
-Upper limit of ingredients, empirically, would be around the 40-45 mark. This could mean that the number of scientific names (if that's what you're asking) associated with a recipy would be triple or four times that.
Thanks for your interest! – Gryzor – 2015-10-01T13:02:29.387