Excel: combine strings baseadas em chaves duplicadas

Question

Eu tenho duas colunas Chave e Valor (mapa padrão)

Existem chaves duplicadas. É trivial para apagar as chaves duplicadas (ou seja, o botão delete duplicates). Mas isso excluiria alguns dados úteis na coluna de valores (ou, alternativamente, bagunça os pares).

O que realmente deve ser feito é que os dados sejam consolidados de forma que os valores sejam concatenados com base em chaves duplicadas. Idealmente, isso seria feito com uma formatação específica em mente, mas isso é menos importante.

Isso parece relativamente simples para valores numéricos, mas menos para requisitos baseados em texto.

Por exemplo link

Eu esperava que a soma fosse equivalente à concatenação; mas infelizmente isso não parece ser o caso.

    

Answer 1

Eu acho que isso soa como uma pergunta semelhante que acabei de responder com o VBA. Para sua solução, copie a coluna Key , remova as duplicatas e use uma fórmula para chamar uma UDF que retorna uma cadeia de valores separados por vírgulas.

Este é o resultado final da pergunta:

Eaquiestáocódigo:

OptionExplicitFunctionLookupCSVResults(lookupValueAsVariant,lookupRangeAsRange,resultsRangeAsRange)AsStringDimsAsString'ResultsplaceholderDimsTmpAsString'CellvalueplaceholderDimrAsLong'RowDimcAsLong'ColumnConststrDelimiter="|||"  'Makes InStr more robust

    s = strDelimiter
    For r = 1 To lookupRange.Rows.Count
        For c = 1 To lookupRange.Columns.Count
            If lookupRange.Cells(r, c).Value = lookupValue Then
                'I know it's weird to use offset but it works even if the two ranges
                'are of different sizes and it's the same way that SUMIF works
                sTmp = resultsRange.Offset(r - 1, c - 1).Cells(1, 1).Value
                If InStr(1, s, strDelimiter & sTmp & strDelimiter) = 0 Then
                    s = s & sTmp & strDelimiter
                End If
            End If
        Next
    Next

    'Now make it look like CSV
    s = Replace(s, strDelimiter, ",")
    If Left(s, 1) = "," Then s = Mid(s, 2)
    If Right(s, 1) = "," Then s = Left(s, Len(s) - 1)

    LookupCSVResults = s 'Return the function

End Function

Para ajudar a mostrar o exemplo, aqui está a fórmula mostrada na célula E1 :

=LookupCSVResults(D1,B1:B7,A1:A7)

E aqui está a versão CSV dos dados no intervalo A1:D7 :

Adam,Red,,Red
Adam,Green,,Green
Adam,Blue,,Blue
Bob,Red,,Yellow
Bob,Yellow,,
Bob,Green,,
Carl,Red,,

    

Answer 2

Eu tenho procurado por toda a internet por isso. Eu modifiquei esta linha:

If lookupRange.Cells(r, c).Value = lookupValue Then

para isso, para que uma correspondência parcial de string funcione:

If InStr(1, lookupRange.Cells(r, c).Value, lookupValue) Then