Encontrar únicos e mostrar valores correspondentes como valores separados por vírgula no Excel?

Muitas maneiras de obter o resultado desejado, aqui estão algumas maneiras que conheço até agora:

▶️ Usando Power Query, disponível em Windows Excel 2010+eExcel 365 (Windows or Mac)

Para usar o Power Query siga as etapas:

• Primeiro converta os intervalos de origem em uma tabela e nomeie-os de acordo. Para este exemplo, eu os nomeei comoTable1

• Em seguida, abra uma consulta em branco em DataTab --> Get & Transform Data--> Get Data--> From Other Sources-->Blank Query

• O acima permite que a Power Queryjanela seja aberta, agora em HomeTab -> Advanced Editor-> E cole o seguinte M-Coderemovendo tudo o que você vê e pressioneDone

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Household", type text}, {"Names", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Household"}, {{"Names", each Text.Combine([Names],", "), type text}})
in
    #"Grouped Rows"

• Por último, para importá-lo de volta para Excel--> Clique em Close & Loadou Close & Load To--> O primeiro que clicar criará um New Sheetcom a saída necessária enquanto o último exibirá uma janela perguntando onde colocar o resultado.

▶️ Usando GROUPBY()a função disponível na MS365 versão Office Insiders :

=GROUPBY(A2:A10,B2:B10,ARRAYTOTEXT,,0)

▶️ Ou, usando LAMBDA()a função auxiliar BYROW()-> Aplicável ao canal atualMS365

=LET(
     _data, A2:B10,
     _household, TAKE(_data,,1),
     _names, UNIQUE(_household),
     HSTACK(_names, BYROW(_names, LAMBDA(x, TEXTJOIN(", ",1,IF(x=_household,TAKE(_data,,-1),""))))))

▶️ Ou, usando POWER PIVOT, disponível em Windows Excel 2013+eExcel 365 (Windows)

Para usar o Power Pivot siga as etapas:

• Primeiro converta os intervalos de origem em uma tabela e nomeie-os de acordo. Para este exemplo, eu os nomeei comoTable_1

• Selecione alguma célula em seus dados e clique em InsertTab -> Clique em Pivot Table-> O Table/Rangeserá mostrado como Table_1, Clique em New Worksheetou Existing Worksheetconforme sua escolha, -> Se último selecione o local da célula e clique em Add this data the Data Model.

• No Pivot Tablepainel de campos direito aparece, clique com o botão direito Table_1no painel e selecioneAdd Measure

• Insira o nome da medida conforme sua escolha e insira a fórmula abaixo na seção de fórmula:

=CONCATENATEX(Table_1,[Names],", ")

• Pressione Ok, selecione Householde coloque em ROWSÁrea enquanto a Medida Nomeada como Name-CommaSeparated(usada neste exemplo, você pode alterá-la) em VALUESÁrea.

• Na Designguia, altere Report Layoutpara Show in Tabular Form, em seguida, clique Grand Totalse selecione Off for Rows and Columnse você obterá a saída desejada.

⏩ Outra versão alternativa em Excel 2016+diante:

=SUBSTITUTE(CONCAT(IF(D2=A$2:A$10,","&B$2:B$10,"")),",",,1)

A fórmula acima precisa pressionar CTRL+ SHIFT+ ENTERao sair do modo de edição apenas para versões mais antigas.