Executa um Vlookup no Excel para várias linhas de dados com o mesmo valor de pesquisa (chave primária)

Question

Eu quero fazer um Vlookup para nomes que tenham várias linhas para a mesma chave primária:

Deal ID (Primary Key) | Name
437                   | Tom Jones
437                   | Frank Thomas
437                   | Mary Smith

O Vlookup só retornará o 1º Nome no registro (Tom Jones). O que eu gostaria que parecesse era isto: Tom Jones, Frank Thomas e Mary Smith, todos na mesma linha.

    

Answer 1

Parece que você está tentando usar o Excel como um banco de dados relacional, que não é tão bom assim. Acima de tudo, a menos que tenha havido uma mudança nos últimos anos (não tive o prazer de usar nada posterior ao Excel 2010), não há função de planilha para transformar um intervalo ou matriz em uma cadeia delimitada. Você terá que criar uma função de planilha VBA que faça isso.

Você pode usar uma combinação de fórmulas de matriz e VBA para fazer o que quiser. Antes de ir mais longe, sugiro strongmente que você use um programa de banco de dados real, se você se encontrar fazendo coisas assim regularmente. Eu abusei do Excel para fazer tarefas simples de banco de dados relacional no passado, mas apenas tarefas simples. Se eu tivesse que fazer algo mais complexo, teria sido extremamente doloroso.

Supondo que seu ID para pesquisa esteja na célula F1 e sua tabela seja chamada de Table1 , insira o seguinte como uma fórmula de matriz (use control- shift-enter, em vez de entrar). SimpleCat é a sua função de planilha de concatenação VBA.

=SimpleCat(IF(Table1[Deal ID (Primary Key)]=$F$1, Table1[Name], ""))

Um SimpleCat que funcionará para essa instância específica é o seguinte:

Function SimpleCat(Args() As Variant) As Variant
    Dim a As Variant
    SimpleCat = ""
    For Each a In Args
        If a <> "" Then SimpleCat = SimpleCat & a & ", "
    Next
    If Len(SimpleCat) > 0 Then SimpleCat = Left$(SimpleCat, Len(SimpleCat) - 2)
End Function

Deixo como um exercício para você expandir em SimpleCat para torná-lo mais genérico, já que ter uma função de concatenação genérica mais completa é útil na caixa de ferramentas de qualquer pessoa.

Explicação:

Quando avaliada como uma fórmula de matriz, a comparação de uma coluna da tabela inteira com um valor único produzirá uma matriz de TRUE s e FALSE s. Usar um IF construirá uma nova matriz, obtendo valores da posição correta da matriz que você está usando no valor se o lado verdadeiro de IF e preenchendo a string vazia que você está usando no valor se lado falso do IF . A função SimpleCat coloca todos os valores não vazios nesse novo array junto com vírgulas entre eles.

    

Answer 2

Dois problemas. Primeiro, isso não é uma chave primária; segundo, não há nenhuma funcionalidade incorporada no Excel que lhe dará o resultado que você procura.

Uma chave primária deve ser um identificador único que esteja ligado a um único registro e que nunca se repita. É com isso que a maioria das funções de Pesquisa no Excel foi projetada para trabalhar, e é por isso que elas param quando encontram uma correspondência - ela economiza recursos para não ter que examinar toda a faixa toda vez que uma correspondência já foi encontrada. p>

A única maneira que eu pude ver para fazer o tipo de consolidação que você está procurando aqui, puxando vários valores em uma única string, seria usar uma macro ou uma função personalizada. Exatamente o código que você precisa dependeria do resultado exato que você procura e está fora do escopo desta resposta, receio.

    

Answer 3

Eu tomaria esta função ...

Function ConcatRange(inputRange As Range, Optional delimiter As String) As String
    Dim oneCell As Range
    Dim usedRange As Range

    Set usedRange = Application.Intersect(inputRange.Parent.usedRange, inputRange.Cells)
    If Not (usedRange Is Nothing) Then
        For Each oneCell In usedRange
            If oneCell.Text <> vbNullString Then
                ConcatRange = ConcatRange & delimiter & Trim(oneCell.Text)
            End If
        Next oneCell
        ConcatRange = Mid(ConcatRange, Len(delimiter) + 1)
    End If
End Function

e modifique-o para criar uma nova função que seria algo como:

Function ConcatIf(KeyRange as Range, KeyValue as Variant, DataColumnOffset As Integer)

onde ele passaria por cada célula dentro de KeyRange e, somente se seu valor for igual a KeyValue, concatene o valor de oneCell.Offset(0, DataColumnOffset)