Duplicate Rows (almost!)

[IanBr]

Hi, hoping someone can help...

I have an excel table of about 16,000 rows

Many of these are duplicates but the info is split between different rows - so for example, for a particular entry there might be an address in column B against the first entry but a phone number in column C against the duplicate.

I want to end up with one table of info containing all available data with no duplicates...is this possible?

 

[JEC]

How about this

Sub jec()
 Dim ar, a As Variant, i As Long
 ar = Cells(1, 1).CurrentRegion
 
 With CreateObject("scripting.dictionary")
   For i = 1 To UBound(ar)
      If Not .exists(ar(i, 1)) Then
        .Item(ar(i, 1)) = Array(ar(i, 1), ar(i, 2), ar(i, 3))
      Else
        a = .Item(ar(i, 1))
        If a(1) = "" Then a(1) = ar(i, 2)
        If a(2) = "" Then a(2) = ar(i, 3)
        .Item(ar(i, 1)) = a
      End If
   Next
   Cells(1, 6).Resize(.Count, 3) = Application.Index(.items, 0, 0)
 End With
End Sub

 

[JEC]

With power query very easy

let
    source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ar = Table.Distinct(Table.FillUp(source,{"Address", "Phone"}), {"Account"})
in
    ar