Transform Table Format From Vertical to Horizontal

Question

I found a similar post to what I want to achieve but the other way around https://superuser.com/questions/683413/transform-horizontal-table-layout-to-vertical-table

I want to transform this

City	Name	Address
City A	Company A	Address A
City B	Company B	Address B
City A	Company C	Address C
City B	Company D	Address D

to this

	Name_1	Address_1	Name_2	Address_2
City A	Company A	Address A	Company C	Address C
City B	Company B	Address B	Company D	Address D

I tried using Pivot Table, Power Query, and measure and ended up with this result.

	Name_1	Address_1
City A	Company A, Company C	Address A, Address C
City B	Company B, Company D	Address B, Address D

I've been stuck on it for hours but I just can't find a way to do it using Pivot Table. I saw articles about VBA explaining this but I dont have experience with it. Will learn VBA as my last resort.

TIA.

Answer 1

Give a try to the following formula-

=HSTACK(UNIQUE(A2:A5),DROP(REDUCE("",UNIQUE(A2:A5),LAMBDA(a,x,VSTACK(a,TOROW(FILTER($B$2:$C$5,$A$2:$A$5=x))))),1))