Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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:

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.

like image 468
Ash Avatar asked Apr 19 '12 13:04

Ash


People also ask

How do I return multiple matches in one cell?

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.

How do I extract multiple matches in Excel?

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.

How do you do a VLOOKUP to show all matches?

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.

How do you collect all the quantity in one cell?

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.


2 Answers

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))))
like image 140
Henrique G. Abreu Avatar answered Oct 16 '22 03:10

Henrique G. Abreu


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.

like image 42
Excellll Avatar answered Oct 16 '22 03:10

Excellll