# 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

##### New Member
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.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
+Fluff v2.xlsm
ABCDEFGHIJKLM
2AName 1AName 1Name 9Name 16
3BName 2BName 2Name 6Name 10Name 19
4CName 3CName 3Name 7Name 12Name 17
5DName 4DName 4Name 18
6EName 5EName 5
7BName 6FName 8Name 11Name 13Name 14Name 15
8CName 7
9FName 8
10AName 9
11BName 10
12FName 11
13CName 12
14FName 13
15FName 14
16FName 15
17AName 16
18CName 17
19DName 18
20BName 19
21
22
23
Master
Cell Formulas
RangeFormula
E2:E9E2=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:M9F2=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

##### Banned - Rules violations
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

Power Query:
``````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``````

Last edited:

#### Akuini

##### Well-known Member
But I'm working with hundreds of thousands of people and addresses

VBA Code:
``````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
ABCDEFG
1
2FFordFFordFranciscoDamian
3PBodePBodeZander
4OJoeOJoeManuel
5GKellenGKellenStephen
6FFrancisco
7PZander
8OManuel
9GStephen
10FDamian
Sheet3

Replies
3
Views
324
Replies
57
Views
3K
Replies
0
Views
389
Replies
4
Views
4K
Replies
0
Views
447

1,129,700
Messages
5,637,882
Members
416,988
Latest member
Ahmed_Yehia

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back