Have a sheet with approx. 900+ rows. I need a formula that will automatically transpose items horizontally if the address is the same. (see pic)

[bgiovanni1]

I am trying to save lots of time and headache, so any help is appreciated! There are many addresses that have multiple tenants. I need the tenants horizontally transposed into the same row so that I can use this as a mail merge. I guess part two of this is that it would be great to also have a way to quickly and easily delete the other duplicates after this is done without having to do it manually, but that is not as important. Thank you in advance for your help!

Brenda

 

[sandy666]

like this?
(part of the table)

Josh	Jason	Nick	Mohammed	Amy	Kareem	Lauren	Mary	Atex	Leah
1274 Washington St	1274 Washington SI	1274 Washington SI	1274 Washmaton Si	1275 E 24th	1275 E 24th	1275 E 24th	1275 Ferry St	1275 Ferry St	1275 Ferry Studio

or show representative source and expected result

 

[bgiovanni1]

 

[bgiovanni1]

So basically, these people live at the same address and are roommates. If I wanted to send a letter to them, it would be one letter to all of them.

 

[sandy666]

So like this?

Address	Tenant.1	Tenant.2	Tenant.3	Tenant.4
1274 Washington St	Josh	Jason	Nick	Mohammed
1275 E 24th	Amy	Kareem	Lauren
1275 Ferry St	Mary	Atex
1275 Ferry Studio	Leah
1293 Tyler 28	Lucas	Logan	Ch*oe	Ryan
1336 HighAlev	Alexandria	Raphael	Jim	Jones
168 N Cleveland St	Mome
1685 High St	Jag

with Power Query M-code:

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Address"}, {{"Count", each _, type table}}),
    Tenant = Table.AddColumn(Group, "Tenant", each List.Distinct(Table.Column([Count],"Tenant"))),
    Extract = Table.TransformColumns(Tenant, {"Tenant", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    Split = Table.SplitColumn(Extract, "Tenant", Splitter.SplitTextByAnyDelimiter({"="}))
in
    Split

 

[bgiovanni1]

Totally You are awesome, thank you! I am going to try it now and will report back.

 

[sandy666]

You are welcome
Have a nice day

 

[bgiovanni1]

So, now I have a whole other problem-- Power query won't start up at all. I will work on that and hope I can use your code!

 

[sandy666]

Power Query is built-in to 365
maybe find : Data - New Query - From Other Sources - Blank Query , then paste code there (I don't know how 365 is organised, sorry )
be aware the table name should be the same, source table name = table name in the code

 

[bgiovanni1]

no, that's exactly what I did. It comes up and freezes. Can't do anything. I will try to troubleshoot.