Spreadsheet formula for collecting all matches in one cell

Question

I have a table that looks like this:

ABCD 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 created a UniqueFamilyId column to truly identify each family.

I am trying to develop a formula that will collect the first names of all those who live at the same address in one cell, i.e. one that populates column E (AllFirstNames).

 ABCDE 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 please help me with this?

I assume that this is some form of finding all the values ​​in column D (UniqueFamilyId) that are the same, and then using vlookup to get the first name, everything in the array formula, to collect them all.

PS I developed how to calculate how many of them live in each address - the formula is simple

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

but I want all these names to be collected, and not just an account.

Answer 1

In Google Spreadsheet, you can use ArrayFormula as follows:

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

Just paste it into cell E2 , then copy.

[edit]

Playing a little more, I knew that all this could be done in one cell (E2). that is, there is no need to copy. Naturally, this is much 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)))) 

Answer 2

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

Step 1:
Press Alt + F11 to open the VBA editor pane in Excel. Insert a new module and paste 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 feature allows you to create comma-separated lists to summarize the tag data that you have.

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

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

Just fill in the column and it should do it.