Spreadsheet formula to gather all matches into one cell

Question

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:

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

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

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.

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.

Answer 1

On Google Spreadsheet you can use an ArrayFormula like this:

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

Just paste it on cell E2 then copy down.

[edit]

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