Excel - retorna vários valores para cada lista de valores

Question

Estou tentando resolver isso há algumas horas, mas sem resultado ...

Minha configuração:

• Eu tenho planilhas do excel de receitas de produtos de cosméticos .

• Cada receita contém dezenas de ingredientes, cada um dado pelo seu nome comercial .

• Eu tenho uma longa lista traduzindo cada nome comercial para o seu nome 'científico' .

Eu preciso traduzir cada nome comercial em sua contraparte científica. Esta parte é muito fácil de fazer com um vlookup.

Mas alguns nomes comerciais são receitas , contendo vários ingredientes na tabela de nomes científicos . Então não é um relacionamento 1: 1, às vezes (nem sempre) é 1: muitos.

Por exemplo:

Cosmética Uma folha

 IngredientA_trade_name

 IngredientB_trade_name

 IngredientC_trade_name

Troque para o arquivo mestre da lista de nomes científicos:

 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.

Agora, encontrei maneiras de retornar vários valores, mas nenhum deles faz o trabalho automaticamente:

Eu preciso colocar o nome Ingrediente em uma célula, depois colocar a fórmula na célula ao lado e arrastá-la para alguns lugares para que ela preencha automaticamente todas as correspondências possíveis. Algumas outras soluções colocam os valores de retorno em uma única célula (Science2, Science3, Science4), que é um pouco melhor, mas não funciona para o resto do meu fluxo de trabalho ...

Existe uma maneira de percorrer a lista de nomes comerciais e inserir linhas, conforme necessário, contendo todos os nomes científicos?

    

Answer 1

Esta macro usando o VBA fará o trabalho:

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

Você precisa abrir Macros / VBA, inserir um módulo em ThisWorkbook e colar o código no lado direito.

Tenha em mente estes:

• O código é executado na planilha ativa.
• Cada novo nome científico, além do primeiro nome comercial, é adicionado em uma nova linha.
• O código está preparado para funcionar, desde que não haja células em branco na coluna A das planilhas.
• Como supõe que o nome da planilha da lista principal é Mestre , você precisa alterar a linha mastername="Master" para o nome apropriado.
• Definir 500 nomes comerciais e 5000 nomes científicos levou 23 segundos.

Answer 2

Para o Excel 2010 ou 2013, eu usaria o suplemento do Power Query para isso. No Excel 2016, o Power Query é incorporado à faixa de opções Dados na seção "Obter e transformar".

O Power Query pode começar a partir de uma tabela do Excel. Tem um comando Mesclar que pode unir Consultas, com uma opção para manter apenas as correspondências. Um resultado da consulta pode ser gravado em uma tabela do Excel.

Seus requisitos levariam alguns minutos para serem projetados no Power Query, sem necessidade de código.

...

Eu fui inspirado por algum feedback útil para expandir essa resposta. Na verdade, criei uma solução de trabalho que você pode baixar do meu OneDrive e experimentar:

link

É o arquivo: Demonstração do Power Query - retorna vários valores para cada lista de valores

Demorei menos de 2 minutos para criar (sem contar a cópia nos dados de entrada e escrever a folha Read Me) e não precisei de nenhum código / função.

A técnica principal é Mesclar e Expandir, conforme descrito aqui:

link