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.