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.

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

Solution 2

Using a VBA function will allow you summarize your matches in one cell. I've used the function below many times to concatenate an array of values -- something the built-in function CONCATENATE() cannot do.

Step 1:
Press Alt+F11 to open the VBA editor pane in Excel. Insert a new module and paste in this code for a custom function.

Public Function CCARRAY(rr As Variant, sep As String)
'rr is the range or array of values you want to concatenate.  sep is the delimiter.
Dim rra() As Variant
Dim out As String
Dim i As Integer

On Error GoTo EH
rra = rr
out = ""
i = 1

Do While i <= UBound(rra, 1)
    If rra(i, 1) <> False Then
        out = out & rra(i, 1) & sep
    End If
    i = i + 1
Loop
out = Left(out, Len(out) - Len(sep))
CCARRAY = out
Exit Function

EH:
rra = rr.Value
Resume Next

End Function

This function will allow you to create comma-separated lists to summarize the tag data you have.

STEP 2:
In E2 in your table, paste in the following formula and press Ctrl+Shift+Enter. This will enter it as an array formula.

=CCARRAY(IF(D2=$D$2:$D$7,$A$2:$A$7)," ")

Simply fill down the column, and that should do it.

Comments

[Ash]

This works, thanks. Will accept if no-one else can find a built in way of concatenating string values from an array formula

[Ash]

Turns out that a slight tweak to your first formula does it in a single cell. If you do =concatenate(ArrayFormula(rept(A:A&" ";D:D=D2))) it returns all of those results in a single answer as concatenate does accept array forumlas but with this nesting the entire thing ends up as a single-cell formula. Mind editing your answer for posterity if I'm right?

[Henrique G. Abreu]

@AshirusNW sorry, but your suggestion works the same. Haven't you tested it? And, as they work the same, I always place the ArrayFormula as the outer formula, wrapping all others, even if not needed, as a "best practice" (which I've learned from Google Docs gurus on Docs forum).