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
Joined
Jan 22, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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:

r/excel - 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?
What I aim to get is:

r/excel - 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?
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!
 

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
Joined
Jun 12, 2014
Messages
56,614
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff v2.xlsm
ABCDEFGHIJKLM
1AddName
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
Joined
Oct 24, 2015
Messages
7,499
you can try with Power Query
Column1Column2Column1List.1List.2List.3
Address 1Armin OlatAddress 1Armin OlatLuise AnsonMarry Reed
Address 2Anna JayAddress 2Anna Jay
Address 1Luise AnsonAddress 3Olson Briar
Address 1Marry Reed
Address 3Olson 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
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
But I'm working with hundreds of thousands of people and addresses
How about vba:

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
 

Watch MrExcel Video

Forum statistics

Threads
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?

Disable AdBlock

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
Top