电子表格公式将所有匹配项收集到一个单元格中 [英] Spreadsheet formula to gather all matches into one cell

问题描述

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

I've got a spreadsheet which looks like this:

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 列以从本质上唯一地标识每个家庭.

I've create the UniqueFamilyId column to essentially identify each family uniquely.

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

I'm trying to work out a formula that will gather the first names of all those who live at the same address into one cell - i.e. one that will fill out column 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 脚本，我不介意.你能帮我实现这个吗?

I suspect that a mixture of vlookup and array formulas will do the trick, but if I have to use Excel VBA or Google Apps Scripts, I don't mind. Can you help me achieve this, please.

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

I'm guessing it's some form of finding all values in column D (UniqueFamilyId) that are the same and then using vlookup to get the first name, all within an array formula to gather them all up.

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

P.S. I've worked out how to count how many live at each address- the formula is simply

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

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

but I want all those names gathered, not merely a count.

推荐答案

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

On Google Spreadsheet you can use an ArrayFormula like this:

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

只需将其粘贴到单元格 E2 上，然后向下复制即可.

Just paste it on cell E2 then copy down.

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

Playing around a little more, I knew it was possible to do it all on a single cell (E2). i.e. no need to copy down. Naturally that it is way more complicated :) But here you go:

=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))))

这篇关于电子表格公式将所有匹配项收集到一个单元格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！