Forum Discussion
Merging multiple persons residing at the same address into one household address
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!
- OliverScheurichGold Contributor
- PeterBartholomew1Silver Contributor
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.
- Riny_van_EekelenPlatinum Contributor
PeterBartholomew1 Indeed, PQ can do this very easily. Couldn't resist to demonstrate it. See attached.
- PeterBartholomew1Silver Contributor
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?
- Romy_YourexCopper Contributor
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!