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.
Vlookup to return multiple values into one cell with TEXTJOIN function (Excel 2019 and Office 365) If you have the higher version of the Excel such as Excel 2019 and Office 365, there is a new function - TEXTJOIN, with this powerful function, you can quickly vlookup and return all matching values into one cell.
This is an array formula and must be entered with Control + Shift + Enter. After you enter the formula in the first cell, drag it down and across to fill in the other cells.
VLOOKUP with Multiple Results To lookup multiple matches with the VLOOKUP Function you need to create a helper column within the table of data. The helper column uses the COUNTIF Function to create a unique ID for each instance. The helper column must be the leftmost column within the data set.
Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and you're done. When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers. Here's an example.
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))))
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With