2

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 Oct 1 '15 at 10:47
  • 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 Oct 1 '15 at 13:02
1

This macro using VBA will do the job:

Public Sub ingredients()
    Dim wkb As Workbook
    Dim wks, wks1 As Worksheet
    Set wkb = ThisWorkbook
    Set wks = ActiveSheet
    wks.Application.ScreenUpdating = False
    mastername = "Master"
    totalsheets = wkb.Worksheets.Count
    For i = 1 To totalsheets
        Set wks1 = wkb.Worksheets(i)
        wks1name = wks1.Name
        If wks1name = mastername Then
            i = totalsheets
        Else
            Set wks1 = Nothing
        End If
    Next i
    reviewing = True
    activerow = 1
    While reviewing
        tradename = wks.Cells(activerow, 1)
        If tradename = "" Then
            reviewing = False
        End If
        reviewingmaster = True
        activerowmaster = 1
        found = 0
        While reviewingmaster
            sciname = wks1.Cells(activerowmaster, 1)
            If sciname = "" Then
                reviewingmaster = False
            End If
            If sciname = tradename Then
                found = found + 1
                If found > 1 Then
                    activerow = activerow + 1
                    wks.Rows(activerow).Insert
                End If
                wks.Cells(activerow, 2) = wks1.Cells(activerowmaster, 2)
                wks.Cells(activerow, 3) = wks1.Cells(activerowmaster, 3)
            End If
            activerowmaster = activerowmaster + 1
        Wend
        activerow = activerow + 1
    Wend
    wks.Application.ScreenUpdating = True
    theend = MsgBox("Finished on " & wks.Name, vbInformation)
End Sub

You have to open Macros /VBA, insert a module under ThisWorkbook and paste the code on the right side.

Keep in mind these:

  • The code runs on the active worksheet.
  • Every new science name besides the first for a trade name, is added in a new row.
  • The code is prepared to work as long as there are not blank cells on the column A of the worksheets.
  • As it supposes that the name of the master list worksheet is Master you have to change the line mastername="Master" to the appropiate name.
  • Setting 500 trade names and 5000 scientific names took it 23 seconds.
  • Oh man. I'm on the road right now but took a look at the code, I think I understand how it works. Seriously, I thought I might be missing something with Excel functions, I didn't think it'd take a macro. Will test it later on. In the meanwhile, where can I send the money? :D Seriously, I'm so grateful. Thank you. – Gryzor Oct 1 '15 at 12:19
  • A function can't insert rows, cells or columns. – jcbermu Oct 1 '15 at 13:01
  • ...and I had forgotten about that. Of course, always used macros for this. Man, must be getting rusty... :D – Gryzor Oct 1 '15 at 13:11
  • (disregard previous comment if you read it, my formatting was wrong...) It works! YAY!!!!!!! While at it, would it be easily ammended to also fetch another column (%) which corresponds to each scientific name? For instance Water (Trade)=aqua (scientific),100%, while X(Trade) can be a=20%, b=50% etc. These percentages are in the next column in Master. Thanks again @jcbermu ... can't appreciate enough! – Gryzor Oct 1 '15 at 13:44
  • @Gryzor Ammended. After the line wks.Cells(activerow, 2) = wks1.Cells(activerowmaster, 2) I put this: wks.Cells(activerow, 3) = wks1.Cells(activerowmaster, 3). – jcbermu Oct 1 '15 at 13:52
1

For Excel 2010 or 2013, I would use the Power Query Add-In for this. From Excel 2016, Power Query is built in to the Data ribbon under "Get and Transform" section.

Power Query can start from an Excel table. It has a Merge command that can join Queries together, with an option to only keep the matches. A Query result can be written to an Excel Table.

Your requirements would take a few minutes to design in Power Query, with no code required.

...

I was inspired by some helpful feedback to expand on this answer. In fact I built a working solution which you can download from my OneDrive and try out:

http://1drv.ms/1AzPAZp

It's the file: Power Query demo - return multiple values for each of a list of values

It took me under 2 minutes to build (not counting copying in the input data and writing the Read Me sheet), and did not require any code/functions.

The key technique is Merge and Expand, as described here:

https://support.office.com/en-us/article/Merge-queries-Power-Query-fd157620-5470-4c0f-b132-7ca2616d17f9

  • Ahhh, how interesting! I'll take a look tomorrow morning and will try to understand how you did it. Exciting! :) Oh, and thanks tons! – Gryzor Oct 2 '15 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.