How can I convert a list of addresses, first and last names so that one unique address appears per row, while the names populate the columns behind?

[Olat]

Hi everyone!
I need to convert a list of addresses, first and last names so that one unique address appears per row, while the names populate the columns behind.
For example, I have:


What I aim to get is:


I've tried using this: Excel formula: Multi-criteria lookup and transpose | Exceljet
{=INDEX(rng1,MATCH(1,($A1=rng2)*(B$1=rng3),0))}
But I'm working with hundreds of thousands of people and addresses, so this one just hangs and the data refreshes really slowly, only when I scroll down. This approach also includes a lot of manual work, since I don't have multiple criteria, just one, so I need to create COUNTIFs for each address, which slows the process even more.
I'm wondering if there is any faster or more streamlined process to accomplish this?
I'm using MS Office 2019 Excel Version.
Let me know if I can provide any further information.
Thanks in advance!

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Add	Name
2	A	Name 1			A	Name 1	Name 9	Name 16
3	B	Name 2			B	Name 2	Name 6	Name 10	Name 19
4	C	Name 3			C	Name 3	Name 7	Name 12	Name 17
5	D	Name 4			D	Name 4	Name 18
6	E	Name 5			E	Name 5
7	B	Name 6			F	Name 8	Name 11	Name 13	Name 14	Name 15
8	C	Name 7
9	F	Name 8
10	A	Name 9
11	B	Name 10
12	F	Name 11
13	C	Name 12
14	F	Name 13
15	F	Name 14
16	F	Name 15
17	A	Name 16
18	C	Name 17
19	D	Name 18
20	B	Name 19
21
22
23
Master

Cell Formulas
Range	Formula
E2:E9	E2	=IFERROR(INDEX($A$2:$A$20,AGGREGATE(15,6,(ROW($A$2:$A$20)-ROW($A$2)+1)/(ISNA(MATCH($A$2:$A$20,E$1:E1,0))),1)),"")
F2:M9	F2	=IFERROR(INDEX($B$2:$B$20,AGGREGATE(15,6,(ROW($A$2:$A$20)-ROW($A$2)+1)/($A$2:$A$20=$E2),COLUMNS($F$2:F$2))),"")

 

[sandy666]

you can try with Power Query

Column1	Column2		Column1	List.1	List.2	List.3
Address 1	Armin Olat		Address 1	Armin Olat	Luise Anson	Marry Reed
Address 2	Anna Jay		Address 2	Anna Jay
Address 1	Luise Anson		Address 3	Olson Briar
Address 1	Marry Reed
Address 3	Olson Briar

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Column1"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "List", each [Count][Column2]),
    Extracted = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    SplitCount = Table.AddColumn(Extracted, "SCount", each List.Count(Text.Split([List],","))),
    MaxCount = List.Max(SplitCount[SCount]),
    Split = Table.SplitColumn(Extracted, "List", Splitter.SplitTextByDelimiter(","), MaxCount)
in
    Split

 

[Akuini]

But I'm working with hundreds of thousands of people and addresses

How about vba:

Sub a1159274a()
Dim i As Long, j As Long, k As Long, n As Long
Dim va, x, z
Dim d As Object

Set d = CreateObject("scripting.dictionary")
d.CompareMode = vbTextCompare

va = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
ReDim vb(1 To UBound(va, 1), 1 To 10000)

For i = 1 To UBound(va, 1)
    If Not d.Exists(va(i, 1)) Then
        d(va(i, 1)) = va(i, 2)
    Else
        d(va(i, 1)) = d(va(i, 1)) & "|" & va(i, 2)
    End If
Next

For Each x In d.keys
    k = k + 1
    vb(k, 1) = x
    j = 1
        For Each z In Split(d(x), "|")
            j = j + 1
            vb(k, j) = z
            If j > n Then n = j
        Next
Next

Range("D2").Resize(k, n) = vb
End Sub

Book1
	A	B	C	D	E	F	G
1
2	F	Ford		F	Ford	Francisco	Damian
3	P	Bode		P	Bode	Zander
4	O	Joe		O	Joe	Manuel
5	G	Kellen		G	Kellen	Stephen
6	F	Francisco
7	P	Zander
8	O	Manuel
9	G	Stephen
10	F	Damian
Sheet3