Merging multiple persons residing at the same address into one household address

[Romy_Yourex]

Hello,
Does anyone happen to know of a way to merge multiple household members, residing at the same address, into one address for the household? 

I.e. Jane Doe, 4 Cherry Lane, T0M X02, Calgary, AB and John Doe, 4 Cherry Lane, T0M X02, Calgary, AB --> make that into one entry. It's a spreadsheet with 2200 rows. 

Thanks!

[#2]

@Romy_Yourex 

=IF(B1&C1&D1=B2&C2&D2,A1&" "&A2,"")

Is this what you want to do?

[#3]

@Romy_Yourex 

Sadly, you might have to buy a new version of 365 for this to work

= LET(
  addr, UNIQUE(Address),
  occupants, MAP(addr, 
    LAMBDA(a, TEXTJOIN(", ",,FILTER(Name,Address=a)))),
  CHOOSE({1,2}, addr, occupants))

My test data wasn't exactly spectacular

I assume Power Query would also do the job, but it wasn't immediately obvious how.

[#4]

@PeterBartholomew1 Indeed, PQ can do this very easily. Couldn't resist to demonstrate it. See attached.

[#5]

@Riny_van_Eekelen 

Thanks for that.  When I tried 'Group', PQ returned a column of Tables, each containing entire records.

Did you get the combined text from the GUI or is it a typed command?

[#6]

@PeterBartholomew1 The Text.Combine step is manipulated by me. Every now and then you need to do that as not everything can get done by the GUI only. When you do the grouping you can first let PQ do a Sum of the names. That creates a piece of M-code like:

each List.Sum([Name])

But that results in Errors as you can't sum texts. But most of the code generated is there. Now, you edit the code to:

each Text.Combine([Name], ",")

A neater way would have been to group without any particular operation. That will produce a column of tables that you then can use to Text.Combine the [Name] columns from.

The full M-code for these two steps look like this:

#"Grouped Rows" = Table.Group(Source, {"Address", "Zip", "City", "State"}, {{"Grouped", each _, type table [Name=text, Address=text, Zip=text, City=text, State=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine ( [Grouped] [Name], "," ))

But, since I'm lazy, I prefer the shorter option.

[#7]

@Riny_van_Eekelen @PeterBartholomew1 @OliverScheurich Thank you so much! That's exactly what I was trying to do and I was able to figure it out with your help!

[#8]

@Riny_van_Eekelen 

Thank you for that.  You confirmed what I thought might be the case.  I followed the solutions as you described them well enough.  Whether I could reproduce them from a blank sheet when needed is another matter entirely!

[#9]

@Riny_van_Eekelen 

As a comment, working with texts we are more safe if at least trim them and use case-insensitive texts for grouping. One variant is to make helper columns, another one add comparer function to grouping. In our case it could be

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    #"Grouped Rows" = Table.Group(
        Source,
        {"Address", "Zip", "City", "State"},
        {
            {"Name(s)", 
            each Text.Combine([Name], ", "),
            type text}},
            GroupKind.Local,
            (current, other) => Number.From( List.ContainsAll(
                List.Transform( Record.FieldValues( current ), each Text.Trim( Text.Lower(_) )),
                List.Transform( Record.FieldValues( other ),   each Text.Trim( Text.Lower(_) ))
                ) ) -1 
   ),

    #"Reordered Columns" = Table.ReorderColumns(
        #"Grouped Rows",
        {"Name(s)", "Address", "Zip", "City", "State"})
in
    #"Reordered Columns"

 Plus misprint with line break is corrected: