google-sheets - 电子表格公式将所有匹配项收集到一个单元格中

Question

我有一个看起来像这样的电子表格:

A             B              C          D                
FirstName     SurnameName    Address    UniqueFamilyId      
---------------------------------------------------------
Abe           Black          1 Elm Ave  :Black:1 Elm Ave:
Joe           Doe            7 Park Ln  :Doe:7 Park Lane:
Jack          Black          1 Elm Ave  :Black:1 Elm Ave:
Bill          Doe            2 5th Ave  :Doe:2 5th Ave:
Harry         Doe            7 Park Ln  :Doe:7 Park Lane:
Sam           Doe            7 Park Ln  :Doe:7 Park Lane:

我创建了 UniqueFamilyId 列以从本质上唯一地标识每个家庭。

我正在尝试制定一个公式，将所有住在同一地址的人的名字收集到一个单元格中 - 即一个将填写列 E (AllFirstNames) 的公式。

A             B              C          D                    E
FirstName     SurnameName    Address    UniqueFamilyId       AllFirstNames
-------------------------------------------------------------------------------
Abe           Black          1 Elm Ave  :Black:1 Elm Ave:    Abe Jack 
Joe           Doe            7 Park Ln  :Doe:7 Park Lane:    Joe Harry Sam
Jack          Black          1 Elm Ave  :Black:1 Elm Ave:    Abe Jack
Bill          Doe            2 5th Ave  :Doe:2 5th Ave:      Bill
Harry         Doe            7 Park Ln  :Doe:7 Park Lane:    Joe Harry Sam
Sam           Doe            7 Park Ln  :Doe:7 Park Lane:    Joe Harry Sam

我怀疑 vlookup 和数组公式的混合可以解决问题，但如果我必须使用 Excel VBA 或 Google Apps 脚本，我不介意。你能帮我实现这个吗？

我猜这是在 D 列(UniqueFamilyId)中查找所有值的某种形式
相同，然后使用 vlookup 获取名字，所有这些都在一个数组公式中以将它们全部收集起来。

附言我已经计算出如何计算每个地址有多少人 - 公式很简单

=COUNTIF(D$1:D$65536,D1)

但我想要收集所有这些名字，而不仅仅是一个计数。

Answer 1

在 Google 电子表格上，您可以使用这样的 ArrayFormula:

=ArrayFormula(concatenate(rept(A:A&" ";D:D=D2)))

只需将其粘贴到单元格 E2然后复制下来。

[编辑]

多玩一点，我知道可以在单个单元格 (E2) 上完成所有操作。即无需复制。当然，它要复杂得多:) 但是你去吧:

=ArrayFormula(transpose(split(concatenate(transpose(if(D2:D=transpose(D2:D);A2:A&" ";"")&if(row(D2:D)=rows(D2:D)+1;char(9);"")));char(9))))